topic Re: Proc Append multiple data sets in SAS Programming

Proc Append multiple data sets

Ronein — Sun, 20 Sep 2020 05:42:43 GMT

Hello

I know that proc append can append no more than 2 data sets.

I want to ask in a situation when I have for example 5 data sets called:

tbl1,tbl2,tbl3,tbl4,tbl5

In such case I can run 4 proc append statements.

Proc append base=tbl2 data=tbl1;run;

Proc append base=tbl2 data=tbl3;run;

Proc append base=tbl2 data=tbl4;run;

Proc append base=tbl2 data=tbl5;run;

Question1:

Is there a way to create macro that make these proc appends automatically?

In real world when I have for example 100 data sets then It is better to create macro that append it instead of typing 99 proc append's

Question2:

The name of the output data set in this example is tbl2.

Is there a way to rename the output wanted dataset to "Wanted"?

Proc append base=tbl2(rename=(tbl2=wanted) data=tbl5;run;

Re: Proc Append multiple data sets

ballardw — Sun, 20 Sep 2020 06:09:00 GMT

If you have a 100 (or other number) of similar named datasets you might consider using a data step. The Set statement can accept lists of names.

Data want;
   set tbl1-tbl100;
run;

Would combine sequentially numbered data sets (no gaps in the numbering).

Data want;
   set tbl: ;
run;

Would append all data sets whose names start with common base Tbl. They would all have to be in the same library though.

Either of these can be combined if you several common base names but not all:

Data want;
   set tbl1-tbl15  tbl_2: ;
run;

Would combine the sequential numbered data sets tbl1 through tbl15 and all the tbl_2 named datasets. The sequence group could be in a different library then the tbl_2 sets.

If the names are "nice" then just a list:

data want; 
    set tbl1 tbl3 tbl27 thatset thisset anotherset;
run;

One advantage of the data set approach is if there are variables that don't appear in all the sets this will work where Proc Append has issues.

For you second question, data set options can't be used to rename anything. You could, with proc append however have the first proc append as

Proc append base=want data=tbl2;
   run;

When the base set does not exist then the append will create it, basically copying tbl2.

Or use Proc Datasets afterwards to rename a data set.

Re: Proc Append multiple data sets

Ronein — Sun, 20 Sep 2020 06:17:02 GMT

Thank you.

Your answer is great but my question was how to create macro that create multiple proc append statements in order to create the desired output.

I know that there is a way to do it via set statement but my question was how to do it with proc append

thanks

Re: Proc Append multiple data sets

Kurt_Bremser — Sun, 20 Sep 2020 07:14:10 GMT

Store the names of the datasets to be appended in a dataset, and use CALL EXECUTE to create the APPEND steps.

Re: Proc Append multiple data sets

Ronein — Sun, 20 Sep 2020 08:20:38 GMT

May you please explain more and show?

%let list=tbl1+tbl2+tbl3+tbl4+tbl5;

Re: Proc Append multiple data sets

Ronein — Sun, 20 Sep 2020 08:37:11 GMT

Will it work well?

In the first run of the loop "wanted" data sets doesn't exist. Will it be a problem?

%macro append;
  %local i;
  %do  i =  1 %to  5;
    proc append base=wanted   data=tbl_&i;
    run;
    %end;
%mend append;
%append;

Re: Proc Append multiple data sets

Ronein — Sun, 20 Sep 2020 08:38:43 GMT

May you please show the full code with Call Execute?

Re: Proc Append multiple data sets

Shmuel — Sun, 20 Sep 2020 08:46:57 GMT

As you insist on a macro program and want the output be called "wanted"

you can use next code:

proc datasets lib=<library> nolist;
    delete wanted;
run;

%macro loop(list);
   %let loop = %sysfunc(countw(&list));
   %do i=1 %to &loop;
          proc append base = wanted data = %scan(&list,&i); run;
   %end;
%mend loop;
%loop( tbl1 tbl2 tbl3 tbl4 tbl5);

Re: Proc Append multiple data sets

Shmuel — Sun, 20 Sep 2020 08:47:30 GMT

In case all input tables have the same prefix (like TBL) and a sequence number (1 to N)

then you can adapt he previous code I have sent to:

proc datasets lib=<library> nolist;
    delete wanted;
run;

%macro loop(prfix,N);
   %do i=1 %to &N;
          proc append base = wanted data = &prefix.&i); run;
   %end;
%mend loop;
%loop(tbl,5);

Re: Proc Append multiple data sets

Ronein — Sun, 20 Sep 2020 09:07:04 GMT

Great.
Why is there ")" here &prefix.&i) ?
was it written by mistake?

Re: Proc Append multiple data sets

Kurt_Bremser — Sun, 20 Sep 2020 09:13:34 GMT

You can work off that macro variable with a %DO loop and %SCAN. We already told you how to do that repeatedly with months stored in your macro list.

@Ronein wrote:

May you please explain more and show?

%let list=tbl1+tbl2+tbl3+tbl4+tbl5;

Re: Proc Append multiple data sets

Kurt_Bremser — Sun, 20 Sep 2020 09:16:31 GMT

I have shown you the use of CALL EXECUTE repeatedly, for instance here:

https://communities.sas.com/t5/SAS-Programming/let-ListDates-and-run-multiple-case-when-statements/td-p/616767

Re: Proc Append multiple data sets

Shmuel — Sun, 20 Sep 2020 09:43:24 GMT

The ')' is part of the %scan from the previous post.
Of course it should be removed.

Re: Proc Append multiple data sets

Ronein — Sun, 20 Sep 2020 11:54:02 GMT

May you please show also the way of using proc append with call execute?

Re: Proc Append multiple data sets

Shmuel — Sun, 20 Sep 2020 14:29:08 GMT

Next code is not tested. Please try it:

%let prefix = TBL;
%let libin  = WORK;  /* input library */
%let libout = WORK;  /* output library */

proc datasets lib=<library> nolist;
    delete wanted;
run;

data _null_;
  set sashelp.vtable
      (where=(libnmae="&libin" and 
	     substr(memname,1,length(strip("&prefix")))="&prefix));
	   call execute("proc append base = &libout.." || "wanted data = &libin.." || memname || "; run;");                    
run;