multi excel files into single excel file with multiple sheets

[KalaBhairava]

Can anybody help me to solve this : I want to convert multi excel files into single excel file with multiple sheets by using sas procedures

[Tom]

---

@KalaBhairava wrote:
Can anybody help me to solve this : I want to convert multi excel files into single excel file with multiple sheets by using sas procedures

---

You would probably get better results using Excel tools to manipulate Excel files.

You could try to convert the original sheets into datasets and then write them back out to a new XLSX file.

Assuming you have XLSX files the flow would look like this:

libname in1 xlsx 'filename1.xlsx';
libname in2 xlsx 'filename2.xlsx';
libname out xlsx 'filename_new.xlsx';
data out.sheet1; set in1.sheet1; run;
data out.sheet2; set in2.sheet1; run;

You will need to take care that the names you give the "datasets" or sheets in the new file are unique, which might require renaming some of them , as in the little example above.

[KalaBhairava]

I have generated excel files by using SAS and I want to combined those individual excel files into single with multiple sheets  without disturbing titles and footnotes.

[Reeza]

Are the Excel sheets created by SAS already? If so, you could modify your code to create a single file - in that case please show an example of your current code. 

Otherwise, I agree with @Tom this is something I would solve using VBS not SAS. 

---

@KalaBhairava wrote:
Can anybody help me to solve this : I want to convert multi excel files into single excel file with multiple sheets by using sas procedures

---

[KalaBhairava]

Yes those are generated by using SAS, for each table I generated separate excel file with different titles and footnotes , now I want to combined those into single file with different sheet without disturbing titles and footnotes.

[Kurt_Bremser]

TITLES and FOOTNOTES suggest your create your Excel files with ODS. So you simply use one starting ODS EXCEL OPTIONS(SHEET_INTERVAL="None") and one ending ODS EXCEL CLOSE statement, and in between ODS EXCEL OPTIONS(SHEET_INTERVAL="Now" SHEET_NAME="xxx") statements.

[Tom · Accepted solution]

If the files already exist then use non-SAS tools to combine them.  Excel or powershell probably have ways to do this via programming.

But if you are making the files you could make the single file to start with.  Or if you need both the individual files and the combined file make them both at the same time.

ods excel (id=multiple) file='bigfile.xlsx' ;

ods excel (id=single) file='single_file1.xlsx';

proc print data=file1;
run;

ods excel (id=single) close;

ods excel (id=single) file='single_file2.xlsx';

proc print data=file2;
run;

ods excel (id=single) close;
ods execl (id=multiple) close;