import specific sheet of an xlsx file into SAS

[hhchenfx]

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

[Tom · Accepted solution]

---

@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

[Kurt_Bremser]

Use LIBNAME XLSX, then you can read the sheet like a dataset and do the correction on the way.