|
SAS system features various functions to enhance output report. This page attempts to show different ways of formatting dates and calculating person's age using SAS dates. SAS date values are numbers and therefore associated with the numeric data type. Its value is the number of days since the beginning of January 1, 1960 and prior to that date, the SAS date value is negative.
Unlike other languages and database management systems, they have a separate data type for dates. SAS does not have, but it has the SAS functions to convert or reformat those dates. In some cases, or rather most cases, dates from a raw data are in character, numeric or pack decimal. To manipulate the date values or to run an algorithm and format the dates, those values need to be converted into SAS dates.
Two of the many SAS functions that I prefer to use for conversion are the INPUT and PUT functions. The INPUT function converts a character value to a numeric value with a numeric informat specification. Whereas, the PUT function applies both conversions with a numeric format specification. In this example, we are going to convert the character dates (in different formats) into SAS® dates as well as numeric dates. I usually use numeric date format to store my data in the database, whichever you are comfortable with.
Note: Proc Format will not be discussed here since it is a different topic.
The record length of these dates varies from 6 to 8 characters. Virtually, the system reads as 8-character value even some of the date have only 6 or 7 characters.
|
data test;
input x $8.;
cards;
4/16/02
04/16/02
5/6/02
5/06/02
12/1/02
12/01/02
;
run;
|
data test2;
set test;
/* convert to SAS date */
newx=input(x,mmddyy8.);
/* reformat to numeric date */
newa=put(newx,yymmddn8.);
newb=put(newx,yymmdd10.);
newc=put(newx,date8.);
run;
proc print;
run;
View Output1
|
|
Also, you have to keep in mind that you can not modify the type of an already defined variable. For instance, if you have character dates of 01/01/2002 or 1/1/2002, the INPUT function converts the strings to SAS dates and creates new variables. Notice that in this example, the dates have 10 characters and we are outputting into two different formats. Once again, SAS system virtually reads this in character length 10.
|
data test;
input x $10.;
cards;
01/01/2000
1/1/2001
01/1/2002
1/01/2003
;
run;
|
data test2;
set test;
SASDate=input(x,mmddyy10.);
run;
proc print;
run;
View Output2
|
|
In this case, the year is before the month and day. The solution is simlpy reversing the format in the INPUT statement.
|
data test;
input x $10.;
cards;
2000/01/01
2001/1/1
2002/01/1
2003/1/01
;
run;
|
data test2;
set test;
SASDate=input(x,yymmdd10.);
run;
proc print;
run;
View Output3
|
|
Suppose your date value is in the form of 19-Oct-00 or 19-Oct-2000 and has been read into a SAS dataset as a character variable. In this example, you can create a new SAS date variable from the character variable by using the INPUT function with the SAS® informat called DATEw. But first you need to look at the SAS® System option of YEARCUTOFF= to see what your site has set as the default value. The YEARCUTOFF= option sets up the 100 year span to determine the century for dates being read in which contain a two digit year.
|
data test;
input x $9.;
cards;
19-Oct-00
19-Oct-01
19-Oct-02
19-Oct-03
;
run;
|
data test2;
set test;
NewA=input(x,Date9.);
NewB=put(NewA,yymmdd10.);
NewC=put(NewA,mmddyy10.);
NewD=put(NewA,yymmn6.);
NewE=put(NewA,yymmddn8.);
NewF=put(NewA,mmddyyn8.);
format NewA Date9.;
run;
proc print;
run;
View Output4
|
|
In some cases where the date value has been already defined as a SAS date or in numeric value i.e., 26DEC02, it is not necessary to use the INPUT function. Users can code directly using the PUT function.
|
data test2;
set test;
NewA=put(SASdate, yymmdd10.); returns the value of 2002-12-26
NewB=put(SASdate, mmddyy10.); returns the value of 12/26/2002
NewC=put(SASdate, yymmn6.); returns the value of 200212
NewD=put(SASdate, yymmddn8.); returns the value of 20021226
NewE=put(SASdate, mmddyy8.); returns the value of 12262002
run;
|
|
This time the record length of the date is 11. Do not forget to modify the format to "Date11" as well.
|
data test;
input x $11.;
cards;
19-Oct-2000
19-Oct-2001
19-Oct-2002
19-Oct-2003
;
run;
|
data test2;
set test;
NewA=input(x,Date11.);
NewB=put(NewA,yymmdd10.);
NewC=put(NewA,mmddyy10.);
NewD=put(NewA,yymmn6.);
NewE=put(NewA,yymmddn8.);
NewF=put(NewA,mmddyyn8.);
run;
proc print;
run;
View Output5
|
|
Now that we know how to convert dates into a SAS date, we are now ready to do mathematical operations. Those dates stored in our SAS dataset are now in the appropriate SAS date format or in numeric date format.
This step uses INTCK to count the number of months between the date of birth (DOB) and the date of service (DOS). Then it divides the number of months by 12 to produce the number of years. While the MONTH determines if the month of the birth date and service date are the same. If so, it determines whether the birthday occured that year of service. If not, it adjusts the age by subtracting one year.
We pulled out a record of Antonio dela Cruz Jr, to calculate his age from the time he had a medical examination from Dr. Indiana Jones. According to our database, Tony was born on 11/3/1972 and Dr. Indy had seen him on 10/20/2001.
|
data test1;
set test;
Age=(intck('month',SASDOB,SASDOS)/12);
if month(SASDOB)=month(SASDOS) then
Age=Age-(day(SASDOB)>day(SASDOS));
run;
proc print;
run;
View Output6
|
|
In my case, I prefer to create a SAS® dataset where the dates are in a numeric data type, so I always end up extra text coding. As I said it before, work whichever you are comfortable with.
|
data test1;
set test;
SASDOS=input(put(DOS,z8.),yymmdd8.);
SASDOB=input(put(DOB,z8.),yymmdd8.);
AGE=(INTCK('MONTH',SASDOB,SASDOS) / 12);
If MONTH(SASDOB)=MONTH(SASDOS) then
Age=Age-(DAY(SASDOB)>DAY(SASDOS));
run;
proc print;
run;
View Output7
|
|
In SAS version8, SAS Institute™ has developed the new YRDIF function to calculate age. What it does, the system output a rounded off result, unlike the result from Output7.
|
data test1;
set test;
SASDOS=input(put(DOS,z8.),yymmdd8.);
SASDOB=input(put(DOB,z8.),yymmdd8.);
Age=int(yrdif(SASDOB,SASDOS,'act/act'));
run;
View Output8
|
To run this in SQL:
proc sql;
create table TEST1 as
select input(put(DOS,z8.),yymmdd8.) as SASDOS,
input(put(DOB,z8.),yymmdd8.) as SASDOB,
int(YDDIF( calculated SASDOB, calculated SASDOS,
'act/act')) as Age
from TEST
;
|
|
I've mentioned about "pack decimal", which you may encounter this kind of format from time to time from a COBOL programmer. On the record layout, it displays as S9(8) and specifies a length of 5. The way to read this in an INPUT statement should be PD5. PD5 is preceeded by two question marks ?? telling the system to ignore or eliminate reading invalid values. These invalid values appear on the SAS log.
|
data test1;
infile test (add any SAS options here);
input
@3452 DateP ?? PD5.
@3457 DateR ?? PD5.
@;
DateProcessed=input(put(DateP,z8.),yymmdd8.);
DateReceived=input(put(DateR,z8.),yymmdd8.);
run;
|
|
By adding format/informat in Data or Proc step, for instance the SAS date=14004, the results are as follows:
|
MMDDYY10. it returns the value of 05/05/1998
MMDDYY8. it returns the value of 05051998
YYMMDD10. it returns the value of 1998-05-05
YYMDD10. it returns the value of 19980505
|
|
Converting a Julian date can be challenging and exciting. This example below is how to convert your julian date into a SAS date or into a numeric date. In this case the julian date is in a character format.
|
data test;
input JD $7.;
cards;
2004098
2004133
;
run;
data test;
set test;
SASDate=input(jd,julian8.);
Date=put(SASDate,yymmdd10.);
run;
|
SAS Output:
Obs JD SASDate Date
1 2004098 16168 2004-04-07
2 2004133 16203 2004-05-12
|
data test;
set carlo.delgrecs (keep=thrudate);
sasdate=input(put(thrudate,z8.),yymmdd8.);
date1=put(sasdate,yymmdd10.);
date2=put(sasdate,mmddyy10.);
run;
proc print;
run;
|
SAS Output:
Obs THRUDATE sasdate date1 date2
1 20011113 15292 2001-11-13 11/13/2001
2 20010913 15231 2001-09-13 09/13/2001
3 20010810 15197 2001-08-10 08/10/2001
|
|
