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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.