Help using Base SAS procedures

SAS 9.2 Importing xlsx date as character $16

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

SAS 9.2 Importing xlsx date as character $16

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.


Accepted Solutions
Solution
‎08-25-2015 01:56 PM
Valued Guide
Posts: 858

Re: SAS 9.2 Importing xlsx date as character $16

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.

View solution in original post


All Replies
Solution
‎08-25-2015 01:56 PM
Valued Guide
Posts: 858

Re: SAS 9.2 Importing xlsx date as character $16

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.

Contributor
Posts: 45

Re: SAS 9.2 Importing xlsx date as character $16

Thank you Mark, that worked great!

Super User
Posts: 3,111

Re: SAS 9.2 Importing xlsx date as character $16

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 499 views
  • 0 likes
  • 3 in conversation