topic Re: SAS 9.2 Importing xlsx date as character $16 in SAS Procedures

SAS 9.2 Importing xlsx date as character $16

Jolly — Tue, 25 Aug 2015 17:27:25 GMT

Hello,

I am seeking help into an issue that has been a major problem for one of my SAS 9.2 programs. I am importing an excel.xlsx file that has 70 columns and 10,000+ rows. For one of the columns the values are dates in excel, but when SAS imports them, they become character $16 variables (Ex. 8/27/2012 11:09). I need these dates to be read into SAS as date9. or some other date format.

NOTE: SAS successfully imports some other date columns correctly with the same date format.

Current import statement:

PROC IMPORT OUT= WORK.data

DATAFILE= "filepath"

DBMS=excel REPLACE;

SHEET="sheetname";

GETNAMES=YES;

MIXED=YES;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

Any suggestions or direction would be great. Thank you,

Jeff S. O.

Re: SAS 9.2 Importing xlsx date as character $16

Steelers_In_DC — Tue, 25 Aug 2015 17:56:11 GMT

Here is a solution for you:

data have;

input date $16.;

cards;

8/27/2012 11:09

;

data want;

format good_date date9.;

set have;

good_date = input(substr(date,1,10),mmddyy10.);

run;

If you want it to import with the desired format I would check those other fields, look in the log and see if there is any additional notation regarding informat / format.

Re: SAS 9.2 Importing xlsx date as character $16

Jolly — Tue, 25 Aug 2015 18:12:06 GMT

Thank you Mark, that worked great!

Re: SAS 9.2 Importing xlsx date as character $16

SASKiwi — Tue, 25 Aug 2015 23:49:55 GMT

You could also try formating the Excel column as a date and avoid a coded solution entirely.