## converting 5 digit date code

Solved
Regular Contributor
Posts: 218

# converting 5 digit date code

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?

Accepted Solutions
Solution
‎07-06-2017 01:45 PM
Super User
Posts: 11,343

## Re: converting 5 digit date code

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

All Replies
Solution
‎07-06-2017 01:45 PM
Super User
Posts: 11,343

## Re: converting 5 digit date code

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

Posts: 3,156

## Re: converting 5 digit date code

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

Super User
Posts: 7,074

## Re: converting 5 digit date code

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

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.

Regular Contributor
Posts: 218

## Re: converting 5 digit date code

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

Super User
Posts: 7,074

## Re: converting 5 digit date code

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

Regular Contributor
Posts: 218

## Re: converting 5 digit date code

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

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:

set info;

if country='usa' then output usa;

if country='Australia' then output Australia;

run;

Super User
Posts: 7,074

## Re: converting 5 digit date code

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

Alice|Sydney|Australia

;;;;

set info;

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

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.

Super User
Posts: 7,832

## Re: converting 5 digit date code

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,832

## Re: converting 5 digit date code

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
🔒 This topic is solved and locked.