topic Re: How to export(append ) output excel file from SAS to an exist excel file? in SAS Procedures

How to export(append ) output excel file from SAS to an exist excel file?

Mike_Davis — Tue, 10 Jul 2012 18:48:58 GMT

Hello everyone,

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.

Thanks!

Mike

Example code:

%macro append;

%do i=1 %to 5;

data have&i;
seq=&i;

run;

      PROC EXPORT DATA=have&i
      OUTFILE = "c:\temp\append.xls"
      DBMS = EXCEL2002 ;
      SHEET = "class";
      RUN;

%end;

%mend append;

%append;

Re: How to export(append ) output excel file from SAS to an exist excel file?

Haikuo — Tue, 10 Jul 2012 19:18:07 GMT

Try using libname engine: libname xl "c:\temp\append.xls";

Then you can treat each individual sheet as SAS table.

Haikuo

Re: How to export(append ) output excel file from SAS to an exist excel file?

PGStats — Wed, 11 Jul 2012 01:28:50 GMT

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 */
data xl.myData;
x = 1;
run;

%macro append;
%do i = 2 %to 5;
data have&i;

x = &i;

run;

proc sql;
/* 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
               union all

select * from have&i;

quit;
%end;
%mend append;

%append;

libname xl clear;