Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

converting 5 digit date code

Accepted Solution Solved
Reply
Regular Contributor
Posts: 218
Accepted Solution

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

View solution in original post


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

Respected Advisor
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
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.

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.

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:

Date1Date2
12/31/201412/31/2014
12/30/201412/30/2014
12/29/201412/24/2014

but after importing to SAS it look like:

Date1Date2
31DEC201442004
30DEC201442003
29DEC201441997

Thanks in advance

Super User
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

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.

Super User
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

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.

Super User
Posts: 7,832

Re: converting 5 digit date code

Your question was already sufficently answered here:

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 4855 views
  • 1 like
  • 5 in conversation