topic Re: converting 5 digit date code in SAS Data Science

converting 5 digit date code

mlogan — Wed, 11 Feb 2015 16:50:01 GMT

Hi All,

When I import my data from excel to SAS, the date field (12/1/2014) comes as 5 digit (41651) code. Can anyone tell me how to turn it back to date (12JAN2014) format in sas?

Re: converting 5 digit date code

ballardw — Wed, 11 Feb 2015 16:59:46 GMT

That is the Microsoft number of days since 1/1/1900 (most likely). To get a better SAS date you likely need

Another recent discussion that should help.

https://communities.sas.com/message/252679#252679

Re: converting 5 digit date code

Haikuo — Wed, 11 Feb 2015 17:03:53 GMT

It seems to me that Excel here to count the days from 01JAN1900, while SAS does it from 01JAN1960. So there is a 60 years of difference. So there maybe something you could do before/during the importing, but if you already has it in SAS table, you can try: INTNX('YEAR', YOURDATE, -60);

Re: converting 5 digit date code

Tom — Wed, 11 Feb 2015 17:43:11 GMT

So assuming you meant 12JAN2014 and not 01DEC2014 then your value is consistent with Excel's date numbering.

You can adjust it easily.

date=date -'01JAN1960'd + '01JAN1900'd -2 ;

format date date9. ;

SAS and Excel don't count the first day the same way so you need to include the 2 day off set.

Re: converting 5 digit date code

mlogan — Thu, 12 Feb 2015 00:42:30 GMT

Hi Tom, I tried 'format date date9.', but it says i am trying to change character value to numeric. Actually in my excel file, date2 field was assigned as text. I formatted the excel data2 field to date format, but SAS is still importing date2 as text. Can you help me a bit more please.

In my excel spreadsheet data look likes:

Date1	Date2
12/31/2014	12/31/2014
12/30/2014	12/30/2014
12/29/2014	12/24/2014

but after importing to SAS it look like:

Date1	Date2
31DEC2014	42004
30DEC2014	42003
29DEC2014	41997

Thanks in advance

Re: converting 5 digit date code

Tom — Thu, 12 Feb 2015 03:30:02 GMT

Neither one of those variable look like they are character variables in SAS. Why did you get the error about the format statement? Perhaps because the variables are named DATE1 and DATE2 instead of DATE?

date2=date2 -'01JAN1960'd + '01JAN1900'd -2 ;

format date2 date9. ;

If DATE2 actually has the character string '42004' instead of the number 42,004 then you will need to make a new variable to hold the converted date.

newdate=input(date2,32.) -'01JAN1960'd + '01JAN1900'd -2 ;

format newdate date9. ;

Re: converting 5 digit date code

mlogan — Sun, 15 Feb 2015 23:12:59 GMT

Hi Tom'

Can you please tell me what would be the code for creating multiple dataset from one dataset in one data step?

Example: I have the following info dataset

Name City Country

John New York USA

Albert Toronto Canada

Alice Sydney Australia

I want to create 3 dataset called USA, Canada and Australia from info dataset above. I tried the following way, but it's not working:

data usa canada australia;

set info;

if country='usa' then output usa;

if country='Canada' then output Canada;

if country='Australia' then output Australia;

run;

can anyone help please. Thanks.

Re: converting 5 digit date code

Kurt_Bremser — Mon, 16 Feb 2015 06:34:48 GMT

Actually, it's two facts:

Excel counts the first day differently (legit), and it has a severe bug by considering 1900 a leap year (very much not legit). That's why there are 2 days difference and not just 1.

Note: LibreOffice/OpenOffice do not have the year 1900 bug and start counting at 31dec1899 to avoid different numbers than Excel. One of the reasons I prefer them over MS Office. There's actually developers/designers with brains there.

Re: converting 5 digit date code

Tom — Tue, 17 Feb 2015 19:06:56 GMT

Make sure the values match what you are testing. In addition to case they might be "hidden" characters in you data such as leading spaces or tabs, carriage returns or other characters that will not be visible when printed.

data info ;

length name city country $20 ;

infile cards dsd dlm='|' ;

input name city country ;

cards;

John|New York|USA

Albert|Toronto|Canada

Alice|Sydney|Australia

;;;;

data usa canada australia;

set info;

if lowcase(country)='usa' then output usa;

if lowcase(country)='canada' then output Canada;

if lowcase(country)='australia' then output Australia;

run;

NOTE: There were 3 observations read from the data set WORK.INFO.

NOTE: The data set WORK.USA has 1 observations and 3 variables.

NOTE: The data set WORK.CANADA has 1 observations and 3 variables.

NOTE: The data set WORK.AUSTRALIA has 1 observations and 3 variables.

Re: converting 5 digit date code

Kurt_Bremser — Wed, 18 Feb 2015 07:10:13 GMT

Your question was already sufficently answered here: