Unable to Read Dates having Custom format(dd/mm/yyyy) from xlsx file using PROC Import

[snigdhaguha01]

Hi,

I am trying read an external .xlsx file using PROC IMPORT.

But the dates stored in the .xlsx file as CUSTOM in dd/mm/yyyy format are not read correctly. only the character variables are read fine.

For e.g. In .xlsx file we have DATE= 01/21/2019 , but after importing the file, the value changes as DATE=43486.

On checking the PROC CONTENTS  for the same variable, DATE, it has TYPE= NUM,LEN=8, FORMAT = BEST.

The DATE is needed in the format of YYYYMMDD.

Please suggest some way out to fix the code.

Thanks !!

[Kurt_Bremser · Accepted solution]

First of all, the usual message:

Excel files are utter CRAP for any kind of data transfer. The only method worse would be clay tablets with data carved in with hammer & chisel.

So you should save your data to a csv file and read that with a data step if you want to have correct and consistent results.

Having said that, it looks like you're getting the raw date values from Excel. Since Excel counts from 30dec1899 (Excel has a well known bug, so it's not 31dec), you need to do this:

data test;
x1 = 43486;
x1 = x1 + '30dec1899'd;
format x1 yymmddd10.;
run;

(line 3 is the important one)

[snigdhaguha01]

Thankyou for the solution. The logic worked here.