Help using Base SAS procedures

Concatenating multiple sheets in multiple excel file using SAS

Reply
New Contributor
Posts: 3

Concatenating multiple sheets in multiple excel file using SAS

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

Super User
Posts: 9,687

Re: Concatenating multiple sheets in multiple excel file using SAS

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

New Contributor
Posts: 3

Re: Concatenating multiple sheets in multiple excel file using SAS

thanks Ksharp...

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

Super User
Posts: 9,687

Re: Concatenating multiple sheets in multiple excel file using SAS

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

Ask a Question
Discussion stats
  • 3 replies
  • 832 views
  • 0 likes
  • 2 in conversation