SAS中如何根据给定日期值识别并定义对应日期格式

Great question! Handling dynamic date format detection in SAS is totally doable, and your initial thought of using substr() is a solid starting point. Let’s build a solution that works for your current examples and can easily adapt to future date formats.

Core Approach #

The key steps are:

1. Convert numeric dates to fixed-length characters: Numeric inputs can lose leading zeros (e.g., 04022018 becomes 4022018 as a number), which breaks substring logic. Use put(raw_date, z8.) to pad to 8 characters with leading zeros.
2. Add validation checks: Don’t just rely on position—verify that components (year, month, day) fall within reasonable ranges to avoid false matches.
3. Build extensible logic: Structure the code so adding new date formats later only requires updating a rule set, not rewriting core logic.

Basic Implementation (For Your Current Formats) #

This code handles yyyymmdd and ddmmyyyy with validation, plus error handling for unrecognized values:

data test;
    /* Read input as 8-character string (or convert numeric to string) */
    input raw_date $8.;
    length formatted_date date9. date_format_used $15.;

    /* Use SELECT-WHEN to test format rules */
    select;
        /* Check for yyyymmdd: valid year (1900-2100) + valid month (1-12) */
        when (input(substr(raw_date, 1, 4), 4.) between 1900 and 2100 
              and input(substr(raw_date, 5, 2), 2.) between 1 and 12) do;
            formatted_date = input(raw_date, yyyymmdd8.);
            date_format_used = 'yyyymmdd';
        end;
        /* Check for ddmmyyyy: valid day (1-31) + valid month (1-12) + valid year */
        when (input(substr(raw_date, 1, 2), 2.) between 1 and 31 
              and input(substr(raw_date, 3, 2), 2.) between 1 and 12
              and input(substr(raw_date, 5, 4), 4.) between 1900 and 2100) do;
            formatted_date = input(raw_date, ddmmyy8.);
            date_format_used = 'ddmmyyyy';
        end;
        /* Handle unrecognized formats */
        otherwise do;
            formatted_date = .;
            date_format_used = 'Unknown';
            put "WARNING: Unrecognized date format for value: " raw_date;
        end;
    end;

    format formatted_date date9.;
    datalines;
20180423
12022018
20231301 /* Invalid month (13) → marked as Unknown */
05062020
;
run;

proc print data=test;
run;

Extensible Version (For Future Formats) #

To make adding new formats painless, use a rule table. You can update this table later without touching the core conversion code:

/* Step 1: Create a table of date format rules */
data date_format_rules;
    input format_name $ validation_logic $;
    datalines;
yyyymmdd input(substr(raw,1,4),4.) between 1900 and 2100 and input(substr(raw,5,2),2.) between 1 and 12
ddmmyyyy input(substr(raw,1,2),2.) between 1 and 31 and input(substr(raw,3,2),2.) between 1 and 12 and input(substr(raw,5,4),4.) between 1900 and 2100
mmddyyyy input(substr(raw,1,2),2.) between 1 and 12 and input(substr(raw,3,2),2.) between 1 and 31 and input(substr(raw,5,4),4.) between 1900 and 2100
;
run;

/* Step 2: Use macro logic to apply rules dynamically */
%macro detect_date_format(input_ds=, output_ds=);
data &output_ds.;
    set &input_ds.;
    length raw_date_char $8. formatted_date date9. date_format_used $20.;
    
    /* Convert numeric input to 8-character string (pad with leading zeros) */
    raw_date_char = put(raw_date, z8.);
    raw = raw_date_char; /* Alias for rule logic compatibility */

    /* Loop through each rule in the table */
    %let num_rules = %sysfunc(countw(&rule_list.));
    %do i=1 %to &num_rules.;
        %let fmt = %scan(&rule_list., &i.);
        %let logic = %sysfunc(sqlobs, select validation_logic from date_format_rules where format_name = "&fmt");
        
        if &logic. then do;
            formatted_date = input(raw_date_char, &fmt..8.);
            date_format_used = "&fmt";
            leave; /* Exit loop once a match is found */
        end;
    %end;

    /* Handle unmatched values */
    if missing(formatted_date) then do;
        date_format_used = 'Unknown';
        put "WARNING: Unrecognized date format for value: " raw_date_char;
    end;

    format formatted_date date9.;
    drop raw raw_date_char;
run;
%mend;

/* Call the macro with your input data and desired formats */
%detect_date_format(input_ds=your_raw_data, output_ds=formatted_dates, rule_list=yyyymmdd ddmmyyyy mmddyyyy);

Key Notes #

• Avoid ambiguity: For dates like 01022018 (could be ddmmyyyy or mmddyyyy), define a priority in your rule order (the first matching rule wins).
• Validate ranges: Adding year/month/day range checks prevents false matches (e.g., a value like 99999999 won’t be incorrectly classified).
• Character first: Always work with character strings for date detection—numeric values can distort leading zeros needed for accurate substringing.

内容的提问来源于stack exchange，提问作者user1645514