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
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;
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
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.
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.
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;
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)
Perfect. Thank you guys.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.