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

[Mike_Davis]

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;

[Haikuo]

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

Then you can treat each individual sheet as SAS table.

Haikuo

[PGStats]

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;

 
PG