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.
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.
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.
Thank you Mark, that worked great!
You could also try formating the Excel column as a date and avoid a coded solution entirely.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.