topic Re: how to load data from multiple sheets of excel file using macro? in SAS Programming

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

ervinodsingh — Wed, 26 Apr 2017 14:00:50 GMT

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

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

RW9 — Wed, 26 Apr 2017 14:12:24 GMT

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;

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

Tom — Wed, 26 Apr 2017 14:25:59 GMT

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;