DATA Step, Macro, Functions and more

How to read and reconcile dates given in two different formats

Reply
Contributor
Posts: 48

How to read and reconcile dates given in two different formats

[ Edited ]

Through a data request, I am working with a database of dates and corresponding measurements. In order to use the dates, I want to make sure they are all in the same format and readable/usable as dates but I am running into some unexpected issues.

 

The date variables are called insurance_dt1 through insurance_dt26. Through proc contents, I can see that all the date variables are numeric with a length of 8., but only 1-15 have an associated date format (YYMMDD10.) whereas 16-26 have no date formatting. Looking closer, I can see why-- some of the date variables were provided to me in a different format that I do not immediately recognize. For example:

 

insurance_dt14   insurance_dt15   insurance_dt16   insurance_dt17   insurance_dt18   insurance_dt19

2013-05-21         2013-06-05        19740                  20355                  19358                 19390

 

I have tried converting them into YYMMDD10. format using the following codes within a datastep, but they usually just turn into missing values or the date 1960-01-02 or 1960-01-03 :

 

new_ins_dt16=input(ins_dt16,yymmdd10.);

    format new_ins_dt16 yymmdd10.;

 

new_ins_dt16 = INPUT(ins_dt16,8.);

    FORMAT new_ins_dt16 YYMMDD10.;

 

new_ins_dt16 = INPUT(PUT(ins_dt16,8.),YYMMDD8.);

    FORMAT new_ins_dt16 YYMMDD10.;

 

I am wondering if I am not properly recognizing the format and thus somehow causing SAS to be unable to read in the dates properly. Alternatively, these dates could be partially incomplete (e.g. missing the exact day), but that does not seem right either since these days visually appear to be more like unformatted SAS dates as opposed to incomplete days. I am not sure, for example, how to interpret something like "20355". Any suggestions would be very much appreciated. Thank you.

PROC Star
Posts: 7,364

Re: How to read and reconcile dates given in two different formats

They are already SAS dates and are only missing the formats. e.g.:

data have;
  informat insurance_dt14-insurance_dt15 yymmdd10.;
  format insurance_dt14-insurance_dt19 yymmdd10.;
  input insurance_dt14-insurance_dt19;
  cards;
2013-05-21         2013-06-05        19740                  20355                  19358                 19390
;

Art, CEO, AnalystFinder.com

Ask a Question
Discussion stats
  • 1 reply
  • 123 views
  • 0 likes
  • 2 in conversation