converting 5 digit date code

[mlogan]

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?

[ballardw · Accepted solution]

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

[Haikuo]

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);

[Tom]

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.

[mlogan]

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

[Tom]

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. ;

[mlogan]

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.

[Tom]

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.

[Kurt_Bremser]

Your question was already sufficently answered here:

[Kurt_Bremser]

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.