import date data from excel

Ronein — Sat, 02 Sep 2017 09:59:45 GMT

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 ?

Re: import date data from excel

Patrick — Sat, 02 Sep 2017 10:22:12 GMT

@Ronein

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

Re: import date data from excel

Tom — Sat, 02 Sep 2017 14:27:47 GMT

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.

topic Re: import date data from excel in SAS Programming

import date data from excel

Re: import date data from excel

Re: import date data from excel