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: 854

Re: Combine two Excel Worksheet

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

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

Grand Advisor
Posts: 16,357

Re: Combine two Excel Worksheet

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.

Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: Combine two Excel Worksheet

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. 

Grand Advisor
Posts: 9,332

Re: Combine two Excel Worksheet

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

Perfect. Thank you guys.

Post a Question
Discussion Stats
  • 7 replies
  • 1431 views
  • 2 likes
  • 6 in conversation