BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mlogan
Lapis Lazuli | Level 10

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

9 REPLIES 9
ballardw
Super User

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
Onyx | Level 15

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

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
Lapis Lazuli | Level 10

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

Tom
Super User Tom
Super User

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
Lapis Lazuli | Level 10

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

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

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

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