topic Re: Combine two Excel Worksheet in ODS and Base Reporting

Combine two Excel Worksheet

JackZhang — Mon, 16 Mar 2015 20:14:19 GMT

Hi,

I had two excel workbook, how i can combine these two excel workbook into one excel workbook with two separate sheets.

What the sas code for doing that? please help.

thank you

Jack

Re: Combine two Excel Worksheet

Steelers_In_DC — Mon, 16 Mar 2015 20:26:53 GMT

If you are talking about exporting to two separate sheets in the same workbook use this template:

ods _all_ close;

ods tagsets.ExcelXP path='PATH' file='OUTPUT.xml'

style=printer;

ods tagsets.ExcelXP options(sheet_name='SHEET NAME ONE' AUTOFILTER = 'ALL');

proc print data=FILE1 noobs;

var _all_;run;quit;

ods tagsets.ExcelXP options(sheet_name='SHEET NAME TWO' AUTOFILTER = 'ALL');

proc print data=FILE2 noobs;

var _all_;run;quit;

ods tagsets.ExcelXP close;

Re: Combine two Excel Worksheet

JackZhang — Mon, 16 Mar 2015 20:46:22 GMT

Hi, Mark,

Thanks. So here data=file1, is this sas data or can be excel file?

Because I try to produce one sheet using SAS but other sheet is produced by excel. Then I try to combine these two sheets into one workbook for report purpose.

And first sheet need to be updated daily by SAS automatically.

That is why I need to integrate two together by SAS

Thank you

Jack

Re: Combine two Excel Worksheet

Reeza — Mon, 16 Mar 2015 22:00:09 GMT

You need to provide more information if you need more explicit help.

Based on what you've mentioned so far, I would use SYSEXEC or X command to copy the file over each day and then PROC EXPORT my data out to the excel file.

Re: Combine two Excel Worksheet

Ksharp — Tue, 17 Mar 2015 08:31:45 GMT

You mean combine two excel files into one excel file ?

libname x1 excel 'c:\temp\x1.xls';

libname x2 excel 'c:\temp\x2.xls';

libname x excel 'c:\temp\x.xls';

data x.'Sheet1$'n;

set x1.'Sheet1$'n;

run;

data x.'Sheet2$'n;

set x2.'Sheet1$'n;

run;

Re: Combine two Excel Worksheet

RW9 — Tue, 17 Mar 2015 09:18:23 GMT

You could also think about it the other way round. Use SAS to generate CSV output each day to a set file. Then in your Excel file has an OnOpen VBA macro which loads that CSV into your Excel file.

Re: Combine two Excel Worksheet

JackZhang — Tue, 17 Mar 2015 20:35:23 GMT

Perfect. Thank you guys.

Re: Combine two Excel Worksheet

Teena1 — Thu, 18 Aug 2016 14:42:45 GMT

Hi Xia,

This code worked for me. But is there any way to keep the formats of the existing excel sheets ? ( for eg - I lost the colours etc in the cobined version)