Having issues formatting dates and using them for calculations

[Geoghegan]

Hello, I have a file where all dates in it are in numeric format. I'm using SAS studio and need to perform calculations based on these dates. I thought I needed to use an informat statement in a data step (creating a new, edited dataset) to tell SAS that the dates are in the format YYMMDD8. because the dates look like 20220211(using today as an example). However, when I did that (or tried to) and then tried to use the dates in the same data step to calculate a new variable (age) based on two dates, the ages don't make any sense because they are very large. 

I used the informat statement:

informat DOB YYMMDD8. ADM YYMMDD8.;

and to calculate age I used:

days=ADM-DOB;

age=floor(days/365.25);

If anyone has any thoughts on why my ages aren't working out properly or if I'm not using the informat statement correctly I'd appreciate it!

[PaigeMiller · Accepted solution]

If the date looks like 20220211 then you have to convert it to something that SAS recognizes as a date.

sas_date = input (put(date,8.),yymmdd8.);

Then to compute the age in years you can use the YRDIF function or the INTCK function on the variable just created called SAS_DATE.

(Do not use days/365.25, this is an approximation, unnecessary as YRDIF or INTCK gets it exactly right, not an approximation).

Also, strictly speaking, this is not a formatting issue, as the YRDIF or INTCK functions will work properly regardless of the format applied to the variable, and will work even if no format is applied. Why? Because SAS always uses the unformatted values to do the calculations.

The issue is that you do not have valid SAS date values, 20220211 is not a valid SAS date value, even though it looks like a date to a human. So when you run the code I provided you get a value of 22687, which is the valid SAS date value for today, even though it doesn't look like one to a human unless you apply a format to it. YRDIF and INTCK and all other SAS functions work properly with valid SAS date values.

[Geoghegan]

Thank you! And for the tip on better calculating dates, that's very helpful!

[Tom]

The INFORMAT statement just attaches an informat to the variable.  Like the LABEL statement attaches a label. For the INFORMAT to do anything you have to use it to convert text into values.  

Where are the values now?  If they are in a text file you are reading then attaching an INFORMAT to the variable might have an effect, depending on how you write the INPUT statement. Attaching an INFORMAT will effect which informat is used by the INPUT statement for that variable when the INPUT statement does not specify an informat to use.

If the data is already in a variable then you need to know what type of variable it has and what values are stored in the variable.

If you have character variable with digit strings like '20220211' or '2022-02-11' then just use an input statement.  You will need to make new variables since date values are numbers.

dob_num = input(dob,yymmdd10.);
adm_num = input(adm,yymmdd10.);

Note: that the INPUT() function does not care if the informat width is larger then the length of the string being read.

If instead you have numeric variables. with numbers like 20,220,211 in them, then you will need to first convert the numbers into strings so you can use the INPUT() function to convert it into a date value.

dob = input(put(dob,z8.),yymmdd10.);
adm = input(put(adm,z8.),yymmdd10.);

To make the values print so that humans can understand them use the FORMAT statement to attach a format that displays date values.  Like YYMMDD10. ;

format dob adm yymmdd10.;

[Geoghegan]

Thank you for your help!!