topic Re: Append an existing file using Proc export in SAS Enterprise Guide

Append an existing file using Proc export

Daily1 — Thu, 16 May 2024 10:36:53 GMT

proc export
data=sashelp.class 
outfile="TEST1"
dbms = xlsx replace;
run;

proc export data=sashelp.class
  outfile="TEST1"
  dbms=xlsx
  replace;
run;

I'm trying to append an existing file using Proc export, but it didn't work.

i want this ouput

Re: Append an existing file using Proc export

LinusH — Thu, 16 May 2024 13:56:23 GMT

I haven't seen a way of controlling this from within PROC EXPORT.

What you can do though is to take the generated data step code generated by PROC EXPORT, and add the MOD option to the FILE statement option:

 /**********************************************************************
  *   PRODUCT:   SAS
  *   VERSION:   9.4
  *   CREATOR:   External File Interface
  *   DATE:      16MAY24
  *   DESC:      Generated SAS Datastep Code
  *   TEMPLATE SOURCE:  (None Specified.)
  ***********************************************************************/
     data _null_;
     %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
     %let _EFIREC_ = 0;     /* clear export record count macro variable */
     file 'c:\Temp\sashelp_class.csv' delimiter=',' DSD DROPOVER lrecl=32767 mod;
     if _n_ = 1 then        /* write column names or labels */
      do;
        put
           "Name"
        ','
           "Sex"
        ','
           "Age"
        ','
           "Height"
        ','
           "Weight"
        ;
      end;
    set  SASHELP.CLASS   end=EFIEOD;
        format Name $8. ;
        format Sex $1. ;
        format Age best12. ;
        format Height best12. ;
        format Weight best12. ;
      do;
        EFIOUT + 1;
        put Name $ @;
        put Sex $ @;
        put Age @;
        put Height @;
        put Weight ;
;
      end;
     if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
     if EFIEOD then call symputx('_EFIREC_',EFIOUT);
run;

Re: Append an existing file using Proc export

Ksharp — Fri, 17 May 2024 01:44:10 GMT

It is impossible for PROC EXPORT to append the data into existed XLSX file.

But you could try LIBNAME + PROC SQL or PROC APPEND to workaround it.

proc export
data=sashelp.class 
outfile="c:\temp\temp.xlsx"
dbms = xlsx replace;
sheet='xxxx';
run;

libname x excel "c:\temp\temp.xlsx"  SCAN_TEXT=NO;
proc append base=x.xxxx data=sashelp.class force;run;
libname x clear;