let’s assume i have a large list of tables say :
%let y = jan feb mar apr may jun jul aug sep oct nov dec
all these tables have the same column names and number of columns, i want to stack all these tables one under the other in one dataset
i tried to create a macro to do so (because i have multiple other lists that are larger than this but up until now i’ve failed to do so
%let y = jan feb mar apr may jun jul aug sep oct nov dec %macro con_tabs(in); %local i s; %do i=1 %to %sysfunc(countw(&in.,%str( ))); %let s&i.=%scan(&in.,&i.,%str( )); proc sql; create table tab&i. as select * from &&s&i...a union all select * from &&s&(i+1)...b; quit; %end; %mend con_tabs;
Advertisement
Answer
If you want to continue to use SQL for this task you will want to properly construct the SQL statement for many tabled UNION ALL
.
Do not iteratively ‘append’ one table at time using a single UNION ALL of the stack and next table. Instead do all stacking in a single SQL statement.
Example:
data t1 t2 t3 t4 t5 t6; retain x 1; run; %macro sql_stack(table_list=, out=); %local index table; proc sql; %do index = 1 %to %sysfunc(countw(&table_list)); %if &index = 1 %then CREATE TABLE &OUT AS /* sql code gen */ ; %else UNION ALL /* sql code gen */ ; %let table = %scan(&table_list, &index); SELECT "&table" as source, * FROM &table /* sql code gen */ %end; ; /* end code gen statement */ QUIT; %mend sql_stack; %sql_stack(table_list=t1 t2 t3 t4 t5, out=want);
Will code gen
CREATE TABLE want AS SELECT "t1" as source, * FROM t1 UNION ALL SELECT "t2" as source, * FROM t2 UNION ALL SELECT "t3" as source, * FROM t3 UNION ALL SELECT "t4" as source, * FROM t4 UNION ALL SELECT "t5" as source, * FROM t5 ; QUIT;