BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jolly
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

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

3 REPLIES 3
Steelers_In_DC
Barite | Level 11

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
Calcite | Level 5

Thank you Mark, that worked great!

SASKiwi
PROC Star

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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