Proc Append multiple data sets

[Ronein]

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;

[ballardw]

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).

Or

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.

[Ronein]

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

[Kurt_Bremser]

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

[Ronein]

May you please explain more and show?

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

[Ronein]

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;

[Ronein]

May you please show the full code with Call Execute?

[Kurt_Bremser]

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/t... 

[Kurt_Bremser]

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;

---

[Shmuel]

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);

[Shmuel · Accepted solution]

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);

[Ronein]

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

[Shmuel]

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

[Ronein]

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

[Shmuel]

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;