Combine two Excel Worksheet

[JackZhang]

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

[Steelers_In_DC]

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;

[JackZhang]

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

[Reeza]

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.

[RW9]

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. 

[Ksharp]

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;

[Teena1]

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)

[JackZhang]

Perfect. Thank you guys.