SAS date conversion from text.

Question

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?

Answer 1

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;

Answer 2

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.

Answer 3

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.