Hi Everyone,
I need to import a sheet of an Excel file (xlsx) into SAS using data step (infile rather than import).
Also, 1 column is date in the format Feb-25-2019 and I don't know how to read it correctly.
Can you please help?
Many thanks,
HHC
@hhchenfx wrote:
Hi Everyone,
I need to import a sheet of an Excel file (xlsx) into SAS using data step (infile rather than import).
Also, 1 column is date in the format Feb-25-2019 and I don't know how to read it correctly.
Can you please help?
Many thanks,
HHC
Nope. An XLSX file is a complex combination of XML files compressed into a ZIP file with and extension of .XLSX instead of .ZIP. Reading that with an INPUT statement is if not impossible, then definitely not something you want to even attempt.
If all of the CELLS in the COLUMN in the worksheet have DATE values then it should not matter how EXCEL is displaying the date, it will come over to SAS as a NUMERIC variable with DATE values. In which case there is nothing you will need to do.
But if the column has a mix of numeric cells (dates are numeric in EXCEL just like in SAS) and character cells then the it will come over to SAS as a CHARACTER variable. In that case you can then run a separate data step to make a new NUMERIC variable with date values.
Let's assume you have created a SAS dataset named HAVE by reading the XSLX sheet (either with PROC IMPORT or by using a libref with the XLSX engine) and it has a variable named XDATE with that strange string of characters in it. Then you could try doing something like this to make a new dataset named WANT and a new variable named DATE.
data want;
set have;
date = input(xdate,anydtdte11.);
format date date9.;
run;
Let's make an example dataset and see if it works:
data have;
xdate='Feb-25-2019';
run;
Result
@hhchenfx wrote:
Hi Everyone,
I need to import a sheet of an Excel file (xlsx) into SAS using data step (infile rather than import).
Also, 1 column is date in the format Feb-25-2019 and I don't know how to read it correctly.
Can you please help?
Many thanks,
HHC
Nope. An XLSX file is a complex combination of XML files compressed into a ZIP file with and extension of .XLSX instead of .ZIP. Reading that with an INPUT statement is if not impossible, then definitely not something you want to even attempt.
If all of the CELLS in the COLUMN in the worksheet have DATE values then it should not matter how EXCEL is displaying the date, it will come over to SAS as a NUMERIC variable with DATE values. In which case there is nothing you will need to do.
But if the column has a mix of numeric cells (dates are numeric in EXCEL just like in SAS) and character cells then the it will come over to SAS as a CHARACTER variable. In that case you can then run a separate data step to make a new NUMERIC variable with date values.
Let's assume you have created a SAS dataset named HAVE by reading the XSLX sheet (either with PROC IMPORT or by using a libref with the XLSX engine) and it has a variable named XDATE with that strange string of characters in it. Then you could try doing something like this to make a new dataset named WANT and a new variable named DATE.
data want;
set have;
date = input(xdate,anydtdte11.);
format date date9.;
run;
Let's make an example dataset and see if it works:
data have;
xdate='Feb-25-2019';
run;
Result
Use LIBNAME XLSX, then you can read the sheet like a dataset and do the correction on the way.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.