topic Re: Reading dates from excel to SAS environment in SAS Programming

Reading dates from excel to SAS environment

sahoositaram555 — Thu, 25 Jun 2020 12:28:28 GMT

Hi experts,

I HAVE AN EXCEL SHEET WHICH I'M IMPORTING IN TO SAS ENVIRONMENT. in excel, i have a column where the dates have been displayed as

28-Oct-17

14-Jan-18

2-Feb-18

13-Jan-18

29-Jan-18

12-May-18

1-Jun-18

28-Aug-18

19-Jun-18

10-Jul-18

When I'm reading in to sas environment. it gives output as and it is a character column

43072
43097
43124
43142
43076
43097
43146
43100
43184
43106

I want the values to be displayed as yymmdd format in sas.

When i CONVERT this column it gives wrong values. Such as for above mentioned sas dates values are displayed as 2077-12-04
2077-12-29
2078-01-25
2078-02-12
2077-12-08
2077-12-29
2078-02-16
2078-01-01
2078-03-26
2078-01-07

the code I'm using is

new_dt=input(dt,best9.);format new_dt yymmdd10.;

Please let me know how should i do to get the right values as posted above( the way it is displayed in the excel).

Re: Reading dates from excel to SAS environment

Kurt_Bremser — Thu, 25 Jun 2020 12:37:01 GMT

You need to take the offset difference between Excel (actually, Lotus 1-2-3) and SAS into account. Change your statement:

new_dt = dt + '30dec1899'd;
format new_dt yymmdd10.;

as I guess that dt is actually numeric. If not, keep the input():

new_dt = input(dt,best.) + '30dec1899'd;

Re: Reading dates from excel to SAS environment

sahoositaram555 — Thu, 25 Jun 2020 13:04:37 GMT

Hi @Kurt_Bremser,

Thank you very much for your kind attention.The coulmn is in character. requesting you to let me know the reason behind using 30dec1899,would like to know more about it.
The output is not coming right, Please have a look to the output i just got and help me know more with your suggestions.
2017-12-03
2017-12-28
2018-01-24
2018-02-11
2017-12-07
2017-12-28
2018-02-15
2017-12-31
2018-03-25
2018-01-06
where as the right output would be
28-Oct-2017
14-Jan-2018
2-Feb-2018
13-Jan-2018
29-Jan-2018
12-May-2018
1-Jun-2018
28-Aug-2018
19-Jun-2018
10-Jul-2018

Re: Reading dates from excel to SAS environment

Kurt_Bremser — Thu, 25 Jun 2020 13:23:49 GMT

43072 IS 2017-12-03 in Excel. Enter the number in Excel and format it as a date.

And in SAS, the conversion works:

 73         data _null_;
 74         date = 43072 + '30dec1899'd;
 75         put date= yymmddd10.;
 76         run;
 
 date=2017-12-03

Why do we use 30dec1899? SAS uses 1960-01-01 as day zero, while Excel uses 1900-01-01 as day 1. But Excel has a bug insofar as it considers 1900 a leap year (a bug taken over from Lotus 1-2-3 and never corrected). Therefore we need to use 1899-12-31 as day 1, and 1899-12-30 as day zero.