topic Re: Proc Import of Date Variables from an Excel XLSX file are coming into my dataset as random numbe in SAS Programming

Proc Import of Date Variables from an Excel XLSX file are coming into my dataset as random numbers

InspectahDex — Fri, 25 Sep 2020 23:35:48 GMT

I have an excel spreadsheet (.xlsx) that has a few fields that are supposed to be dates. Most cells are in a MM/DD/YYYY format but there are other cells that have the word "NULL". When I do a PROC IMPORT, all of the fields come in as character variables and all of the date values are turned into random numbers like 34563. How can I import these date values as the values that they are using PROC IMPORT? Guessingrows doesn't work.

Re: Proc Import of Date Variables from an Excel XLSX file are coming into my dataset as random numbe

SASKiwi — Sat, 26 Sep 2020 00:04:22 GMT

Change the Excel column type of the date columns to "Date" and see if that helps. Having non-missing dates in the first few rows may also help.

Re: Proc Import of Date Variables from an Excel XLSX file are coming into my dataset as random numbe

InspectahDex — Sat, 26 Sep 2020 00:51:07 GMT

The solution wasn't exactly this but it led me on the right track: I replaced the word "NULL" from the excel spreadsheet and reran the SAS program. My date fields were then imported properly.

Re: Proc Import of Date Variables from an Excel XLSX file are coming into my dataset as random numbe

SASKiwi — Sat, 26 Sep 2020 03:33:39 GMT

OK. The typical I'll accept anything you type into a column problem with Excel. AFAIK you can't "lock" the type of column to only accept valid data for that type.