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

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

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

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


Accepted Solutions
Solution
‎04-28-2017 06:00 AM
Super User
Super User
Posts: 7,039

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

Posted in reply to ervinodsingh

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;

 

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

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

Posted in reply to ervinodsingh

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;
Solution
‎04-28-2017 06:00 AM
Super User
Super User
Posts: 7,039

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

Posted in reply to ervinodsingh

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 110 views
  • 1 like
  • 3 in conversation