03-16-2015 04:14 PM
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.
03-16-2015 04:26 PM
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'
ods tagsets.ExcelXP options(sheet_name='SHEET NAME ONE' AUTOFILTER = 'ALL');
proc print data=FILE1 noobs;
ods tagsets.ExcelXP options(sheet_name='SHEET NAME TWO' AUTOFILTER = 'ALL');
proc print data=FILE2 noobs;
ods tagsets.ExcelXP close;
03-16-2015 04:46 PM
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
03-16-2015 06:00 PM
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.
03-17-2015 05:18 AM
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.
03-17-2015 04:31 AM
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';
08-18-2016 10:42 AM
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)