@maxjiang6999:
The main direction of your intended method is right since APPEND is the fastest tool for the job. It's faster than concatenating the files in the DATA step or UNIONizing them via SQL, because APPEND stacks the DATA= file atop the BASE= file as a block, thus avoiding extruding every record through the PDV. As to MERGE, it's a wrong tool for the job - you want to stack the files vertically, while MERGE does it, roughly speaking, horizontally.
However, your APPEND syntax is wrong: It has no OUT= option but merely appends DATA= to BASE=. Furthermore, there's no need to create the interim files TEST1, TEST2, etc. Hence, your process should look schematically as follows:
proc append base = master data = dsn1 (keep = &outcome_vars) ; run ;
proc append base = master data = dsn2 (keep = &outcome_vars) ; run ;
proc append base = master data = dsn3 (keep = &outcome_vars) ; run ;
proc append base = master data = dsn4 (keep = &outcome_vars) ; run ;
proc append base = master data = dsn5 (keep = &outcome_vars) ; run ;
proc append base = master data = dsn6 (keep = &outcome_vars) ; run ;
proc append base = master data = dsn7 (keep = &outcome_vars) ; run ;
proc append base = master data = dsn8 (keep = &outcome_vars) ; run ;
proc append base = master data = dsn9 (keep = &outcome_vars) ; run ;
Frankly, with only 9 files to append, not quite tedious. But supposing that in a different situation you can have more, you may want to generate code like above given the number of files N as a parameter. There're many ways to do this. Using a macro usually comes to mind first, especially since in this case it's very simple:
%macro stack (base=, prefix=, keep=, N=) ;
%do i = 1 %to &N ;
proc append base = &base data = &prefix&i (keep = &keep) ;
run ;
%end ;
%mend ;
%stack (base=master, prefix=dsn, keep=&outcome_vars, N=9)
Some people like CALL EXECUTE better:
data _null_ ;
retain base "master" prefix "dsn" keep "&outcome_vars" N 9 ;
do i = 1 to N ;
call execute (catx (" ", "proc append base=", base, "data=", cats (prefix, N), "(keep=", keep, "); run;")) ;
end ;
run ;
Suum cuique.
Kind regards
Paul D.
... View more