BookmarkSubscribeRSS Feed
meghanrm
Calcite | Level 5

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!

1 REPLY 1
andreas_lds
Jade | Level 19

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 786 views
  • 0 likes
  • 2 in conversation