how to load data from multiple sheets of excel file using macro?

[ervinodsingh]

Hi i have a excel file which has multiple sheets with name Jan, Feb, March, April, ....Dec. I want to load this data for further use using macro.

Thankyou

[RW9]

First rule of programming club, macro is never needed.  What version of SAS/Excel are you using?  If your using 9.4 then the simplest way to do it is to use the libname excel method:

libname myxlsx excel "<path to file>\<filename>.xlsx";

...

libname myxlsx clear;

The libname excel will open an XLSX file, and create a dataset for each sheet it find which is compatible.  You can then use simple statements like:

libname myxlsx excel "<path to file>\<filename>.xlsx";

data want;
  set myxlsx.'Sheet 1'n;
run;

libname myxlsx clear;

[Tom · Accepted solution]

If you are using SAS 9.4 (since I think TSM2?) then just use the XLSX engine on a libname statement and you can treat the sheets as if they were datasets.  This does not require Excel and so works on Unix in addition to Windows, but I think it does require that you have SAS/Access to PC files licensed.

I find it is useful to just use PROC COPY to copy all of the sheets into WORK datasets and then I can use them from there.

libname in xlsx 'myfilename.xlsx';
proc copy inlib=in outlib=work;
run;