The SAS Output Delivery System and reporting techniques

Combine two Excel Worksheet

Reply
Occasional Contributor
Posts: 9

Combine two Excel Worksheet

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

Valued Guide
Posts: 860

Re: Combine two Excel Worksheet

Posted in reply to JackZhang

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;

Occasional Contributor
Posts: 9

Re: Combine two Excel Worksheet

Posted in reply to Steelers_In_DC

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 Smiley Sad

Thank you

Jack

Super User
Posts: 19,822

Re: Combine two Excel Worksheet

Posted in reply to JackZhang

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.

Super User
Super User
Posts: 7,970

Re: Combine two Excel Worksheet

Posted in reply to JackZhang

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. 

Super User
Posts: 10,035

Re: Combine two Excel Worksheet

Posted in reply to JackZhang

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;

Occasional Learner
Posts: 1

Re: Combine two Excel Worksheet

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)

Occasional Contributor
Posts: 9

Re: Combine two Excel Worksheet

Posted in reply to JackZhang

Perfect. Thank you guys.

Ask a Question
Discussion stats
  • 7 replies
  • 1933 views
  • 2 likes
  • 6 in conversation