DATA Step, Macro, Functions and more

import date data from excel

Reply
Regular Contributor
Posts: 199

import date data from excel

Hello

I am using data step to import data from excel.

I have 1 column with date values that are displayed in excel with "Number " format.

(For example : in excel value 42980 represnt  02SEP2017

In sas data step i define the date varaible as a numeric and give a format ddmmyy10.

The problem is that the date appears in SAS is not 02SEP2017!

 

I found a solution to change the format in excel to 3/14/2001 (means  mm/dd/yyyy).

The problem is that my client doesn't want to change the format in excel.

 

Can you offer me a solution ?

Respected Advisor
Posts: 4,780

Re: import date data from excel

@Ronein

Please provide your code and ideally also attach a sample Excel file which illustrates the issue when using your code.

Super User
Super User
Posts: 8,273

Re: import date data from excel

[ Edited ]

Normally SAS will automatically adjust the dates for you.  Where I have seen it fail is when you have dates and character strings in the same column so that SAS imports the field as character instead of numeric. When that happens it pulls over the internal number that Excel uses for the data as a string of digits.  I have never seen it do that and put the number into a numeric field instead, but perhaps you had a column with mixed dates and other numbers so that SAS decided the column was numeric, but did not think it was a date?

 

You just need to adjust the offset.  SAS uses 01JAN1960 and Excel uses 01JAN1900.  Then -2 in the formula is because SAS starts from zero and Excel starts from one and Excel mistakenly thinks that 1900 was a leap year. (So if you have dates that are before 01MAR1900 the result will be off by one).

 

data test ;
  mydate=42980;
  shouldbe='02SEP2017'd;
  put 'BEFORE: ' mydate= comma7. +1 mydate date9. +1 shouldbe= date9. ;
  mydate = mydate + '01JAN1900'd -2 ;
  put 'AFTER : ' mydate= comma7. +1 mydate date9. +1 shouldbe= date9. ;
  format mydate shouldbe date9. ;
run;
2115  data test ;
2116    mydate=42980;
2117    shouldbe='02SEP2017'd;
2118    put 'BEFORE: ' mydate= comma7. +1 mydate date9. +1 shouldbe= date9. ;
2119    mydate = mydate + '01JAN1900'd -2 ;
2120    put 'AFTER : ' mydate= comma7. +1 mydate date9. +1 shouldbe= date9. ;
2121    format mydate shouldbe date9. ;
2122  run;

BEFORE: mydate=42,980  03SEP2077 shouldbe=02SEP2017
AFTER : mydate=21,064  02SEP2017 shouldbe=02SEP2017
NOTE: The data set WORK.TEST has 1 observations and 2 variables.

 

Ask a Question
Discussion stats
  • 2 replies
  • 455 views
  • 0 likes
  • 3 in conversation