SAS 9.2 Importing xlsx date as character $16

[Jolly]

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.

[Steelers_In_DC · Accepted solution]

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.

[Jolly]

Thank you Mark, that worked great!

[SASKiwi]

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