topic Re: multi excel files into single excel file with multiple sheets in SAS Programming

multi excel files into single excel file with multiple sheets

KalaBhairava — Tue, 14 Dec 2021 13:02:07 GMT

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

Re: multi excel files into single excel file with multiple sheets

Tom — Tue, 14 Dec 2021 13:44:01 GMT

@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.

Re: multi excel files into single excel file with multiple sheets

Reeza — Tue, 14 Dec 2021 16:46:32 GMT

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

Re: multi excel files into single excel file with multiple sheets

KalaBhairava — Wed, 15 Dec 2021 05:57:57 GMT

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.

Re: multi excel files into single excel file with multiple sheets

KalaBhairava — Wed, 15 Dec 2021 06:01:22 GMT

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.

Re: multi excel files into single excel file with multiple sheets

Kurt_Bremser — Wed, 15 Dec 2021 10:33:52 GMT

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.

Re: multi excel files into single excel file with multiple sheets

Tom — Wed, 15 Dec 2021 15:08:14 GMT

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;