dates not formatting correctly

[axescot78]

I'm trying to convert variables in string format into date9 format with the following code:

/* Step 1: Clean the date variables */
data svc_dates_cleaned;
    set out.svc_data;

    /* Convert substrings to numeric values */
    snap_yy_num = input(substr(FILE_DT, 1, 4), 4.);
    snap_mm_num = input(substr(FILE_DT, 5, 2), 2.);
    snap_dd_num = input(substr(FILE_DT, 7, 2), 2.);

    if snap_yy_num = 0 then snap_yy_num = .;
    if snap_mm_num = 0 then snap_mm_num = .;
    if snap_dd_num = 0 then snap_dd_num = .;

    if not missing(snap_yy_num) and not missing(snap_mm_num) and not missing(snap_dd_num) then 
        snap_date = mdy(snap_mm_num, snap_dd_num, snap_yy_num);
    else 
        snap_date = .;

    /* Convert DOB substrings to numeric values */
    DOB_ya_num = input(substr(DOB_c, 1, 4), 4.);
    DOB_ma_num = input(substr(DOB_c, 5, 2), 2.);
    DOB_da_num = input(substr(DOB_c, 7, 2), 2.);

    if DOB_ya_num = 0 then DOB_ya_num = .;
    if DOB_ma_num = 0 then DOB_ma_num = .;
    if DOB_da_num = 0 then DOB_da_num = .;

    if not missing(DOB_ya_num) and not missing(DOB_ma_num) and not missing(DOB_da_num) then 
        DOB = mdy(DOB_ma_num, DOB_da_num, DOB_ya_num);
    else 
        DOB = .;

    drop snap_yy_num snap_mm_num snap_dd_num DOB_ya_num DOB_ma_num DOB_da_num;
run;

/* Step 2: Apply the date format */
data svc_dates_formatted;
    set svc_dates_cleaned;
    format snap_date DOB date9.;
run;

I get the following error:

904  data svc_dates_formatted;
905  set svc_dates_cleaned;
906  format snap_date DOB date9.;
                          ------
                          48
ERROR 48-59: The format $DATE was not found or could not be loaded.907  run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.SVC_DATES_FORMATTED may be incomplete.  When this step was stopped there
         were 0 observations and 14 variables.
WARNING: Data set WORK.SVC_DATES_FORMATTED was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      cpu time            0.00 seconds

How do I resolve this?

The character variables are being converted to serial numbers but it is not formatting correctly. I tried making sure all variables are not 0 and being converted to numeric before going into the mdy function. Still getting this error.

[data_null__]

If you want to convert date strings to SAS Date you need to read the data with the proper INFORMAT.

49         data _null_;
50            file_dt = '20240627';
51            x = input(file_dt,yymmdd8.);
52            put x= x=date9.;
53            run;

x=23554 x=27JUN2024

[Kurt_Bremser · Accepted solution]

Look at the log of your first DATA step. You'll find a NOTE about the automatic conversion of numeric to character because the character variable snap_date is already present in the dataset.

[ballardw]

If you know that you have invalid or partial date values you can modify an input such as @data_null__ suggests to suppress the invalid data messages by adding the ?? modifier:

data example;
   input dob_c $;
   dob = input(dob_c, ?? yymmdd10. );
   format dob date9.;
datalines;
20240101
20240500   <= invalid day  0
20192105   <= invalid month 21
;