Fixing inconsistent DOB/dates

[meghanrm]

Hi,

I was given an excel sheet that has a date of birth (DOB) variable which has been recorded in the following formats:

49y

2/23/1970/49y

2/23/1970

I ultimately would like to remove the day to have the DOB in just month/year format (02/1970) and create a new age variable. However, I am hung up on how to even get this into a uniform date format. Any help would be greatly appreciated!

[andreas_lds]

I will forgo the usual comments on Excel as data-source.

You need a date to calculate the dob from strings like "49y". One could use today(), but that will give wrong results, if the code is executed next year.

Assuming that you are familiar with concepts of formats and informats, you could start with:

data have;
   length dobString $ 20;
   input dobString;
   datalines;
49y
2/23/1970/49y
2/23/1970
;

data want;
   set have;
   
   length dob 8 years 8 helper $ 20;
   format dob mmyys7.;
   
   /* try to create a sas-date */
   dob = input(dobString, ?? mmddyy10.);
   
   if missing(dob) then do;
      /* dobString is not a sas-date */
      if countc(dobString, '/') = 0 then do;
         years = input(compress(dobString,, 'kd'), best.);
         /* the date-constant needs to replaced by the date on which the data was recorded */
         dob = intnx('years', '01Apr2021'd, years * (-1), 's');
      end;
      else do;
         helper = substr(dobString, 1, findc(dobString, '/', 'b')-1);
         dob = input(helper, ?? mmddyy10.);
      end;
   end;
   
   drop years helper;
run;