SAS date conversion from text.

0
votes

Hi I have a date conversion problem in SAS, I imported an excel file which has the following dates., 

2012-01-09
2011-01-31
2010-06-28
2005-06-10
2012-09-19
2012-09-19
2007-06-12
2012-09-20
2004-11-01
2007-03-27
2008-06-23
2006-04-20
2012-09-20
2010-07-14

after I imported the dates have changed like this

40917
40574
40357
38513
41171
41171
39245
41172
38292
39168
39622
38827
41172
40373

I have used the input function to convert the dates but it gives a strange result., the code I used., 

want_date=input(have_date, anydtdte12.);
informat want_date date9.; format have_date date9.;run;

I get very stange and out of the World dates., any idea how can I convert these?

3
datesas
Can you explain more clearly what the issue is here? Are the 5-digit numbers you are listing the data after import into SAS? What is the format/informat of that field? – JustinJDavies
The numbers are Excel's numeric representation of dates. – Joe
@Joe - I meant, are the numbers in the second list how the values appear in SAS? – JustinJDavies
Yep, if SAS doesn't detect the field as Date/Datetime it will show the underlying numerics (so, that). – Joe

3 Answers

2
votes

You can encourage SAS to convert the data as date during the import, although this isn't necessarily a panacea. 

proc import file=whatever out=whatever dbms=excel replace;
  dbdsopts=(dbSasType=( datevar=date ) );
run;

where datevar is your date column name. This tells SAS to expect this to be a date and to try to convert it.

See So Your Data Are in Excel for more information, or the documentation.

1
votes

From : http://www2.sas.com/proceedings/sugi29/068-29.pdf

Times are counted internally in SAS as seconds since midnight and date/time combinations are calculated as the number of seconds since midnight 1 January 1960.

Excel also uses simple numerical values for dates and times internally. For the date values the difference with the SAS date is only the anchor point. Excel uses 1 January 1900 as day one.

So add a constant.

EXAMPLES:

SAS_date = Excel_date - 21916;
SAS_time = Excel_time * 86400;
SAS_date_time = (Excel_date_time - 21916) * 86400;
0
votes

As Justin wrote you need to correct for the different zero date (SAS vs. Excel).

Then you just need to apply a format (if you want to get a date variable to do calculations):

want_date = have_date-21916;
format want_date date9.;

Or convert it to a string:

want_date = put(have_date-21916, date9.);

In either case you can choose the date format you prefer.