07-10-2012 02:48 PM
Does anyone has idea of creating output excel file without replace but append to original file?
usually specify REPLACE in proc export will override the whole excel file. but I want to keep the original rows, just want to add new rows to the excel file.
%do i=1 %to 5;
PROC EXPORT DATA=have&i
OUTFILE = "c:\temp\append.xls"
DBMS = EXCEL2002 ;
SHEET = "class";
07-10-2012 03:18 PM
Try using libname engine: libname xl "c:\temp\append.xls";
Then you can treat each individual sheet as SAS table.
07-10-2012 09:28 PM
There are many limitations to update operations with Excel. Here is what works, at least with Excel 2003 and Excel 2007 :
libname xl Excel "&SASForum.\Datasets\Append.xls";
/* Create the Append.xls workbook with a sheet named myData$ and a named range
called myData */
x = 1;
%do i = 2 %to 5;
x = &i;
/* Save the contents of the Excel table */
create table have as
select * from xl.myData;
/* Erase the contents of the table in the workbook, Excel doesn't allow you
to overwrite an existing table */
drop table xl.myData;
/* Recreate the table with your added lines. It will replace the old version
at the same place, with the same cell formatting */
create table xl.myData as
select * from have
select * from have&i;
libname xl clear;