topic Re: Concatenating multiple sheets in multiple excel file using SAS in SAS Procedures

Concatenating multiple sheets in multiple excel file using SAS

Raman — Thu, 12 Apr 2012 06:49:34 GMT

Hi all

I have n number of excel sheet.

Each excel sheet has 2 worksheet.

I want to prepare master sheet where each excel sheet worksheet 1 is concatenated and put in to master excel worksheet 1

and similarly each excel sheet worksheet 2 is combined and put into same master excel but worksheet 2.

All teh sheet has same column names and that is not a problem

Can any of you let me know is it possible to do this using SAS and if so can you let me know how to approach this ?

Thanks

Ramanan

Re: Concatenating multiple sheets in multiple excel file using SAS

Ksharp — Thu, 12 Apr 2012 09:04:51 GMT

Yes. You can do it . Using Libname + excel to operate these Sheets just like SAS datasets.

Re: Concatenating multiple sheets in multiple excel file using SAS

Raman — Thu, 12 Apr 2012 10:08:00 GMT

thanks Ksharp...

If it is possible can you pls post the sample code here..that would be useful for me to develop from here,,,

Re: Concatenating multiple sheets in multiple excel file using SAS

Ksharp — Fri, 13 Apr 2012 03:06:09 GMT

OK. Here is an example.

libname a1 excel 'c:\a1.xls' getnames=yes;
libname a2 excel 'c:\a2.xls' getnames=yes;
data sheet1;
 set a1.'Sheet1$'n a2.'Sheet1$'n;
run;
data sheet2;
 set a1.'Sheet2$'n a2.'Sheet2$'n;
run;


proc export data=sheet1 outfile='c:\main.xls' dbms=xls ;
                         sheet='Sheet1'; putnames=yes; run;
proc export data=sheet2 outfile='c:\main.xls' dbms=xls ;
                         sheet='Sheet2'; putnames=yes; run;

libname _all_ clear;

Ksharp