topic Re: Unable to Read Dates having Custom format(dd/mm/yyyy) from xlsx file using PROC Import in New SAS User

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

snigdhaguha01 — Wed, 30 Jan 2019 14:45:24 GMT

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 !!

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

Kurt_Bremser — Wed, 30 Jan 2019 14:59:43 GMT

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)

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

snigdhaguha01 — Thu, 31 Jan 2019 09:47:15 GMT

Thankyou for the solution. The logic worked here.