Amadeus cookies policy - you'll see this message only once.

Amadeus use cookies on this website. They help us to know a little bit about you and how you use our website, which improves the browsing experience and marketing - both for you and for others. They are stored locally on your computer or mobile device. To accept cookies, continue browsing as normal. Or, go to the privacy policy for more information.

Undo Append

This tip is a utility macro that allows a proc append to be re-run without adding the same data again. This is particularly useful if the appended data is found to have errors after the first run and we want to 'undo' the original append and add back in the correct data. The process uses a control table to record the number of observations in the base table so that the SAS session can be closed between separate runs of the process. This could also be included as part of a batch process.

The parameters for the process are as follows:

  • re_run - Accepts values of TRUE or FALSE to determine whether to undo a previous append.
  • base - The table having data added to.
  • data - The table being added to the base.
  • control_table -  location of the control table.
libname config 'c:temp';


%macro
append_data(re_run=false,base=,data=,control_table=config.control);
  %if %upcase(&re_run)=FALSE %then
  %do;
    %global num_obs;
    %*Get the number of observations in the table;
    %let rc=%sysfunc(open(&base));
    %let num_obs=%sysfunc(attrn(&rc,nobs));
    %let close_rc=%sysfunc(close(&rc));
    %*write the observation to a SAS table so not reliant on
     the global num_obs variable;
    data &control_table;
      obs=input("&num_obs",8.);
    run;
  %end;
  %else
  %if &re_run=TRUE %then
  %do;
    %*check that SAS has not been closed since last run;  
    %if not(%symexist(num_obs)) %then
    %do;
       %global num_obs;
      data _null_;
        set &control_table;
         call symputx('num_obs',obs);
       run;
     %end;
    data &base;
      set &base (obs=&num_obs);
     run;
  %end;
  proc append base=&base data=&data force;
  run;
%mend append_data;


*normal run;
%append_data(base=test,data=test2);
*re-run;
%append_data(base=test,data=test2,re_run=TRUE);