CODEWITHSUNDEEP
sqlsas
Blueberry
Blueberry

Reputation: 363

concatenating large number of tables in sas

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;

Upvotes: 0

Views: 142

Answers (2)

Richard
Richard

Reputation: 27508

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;

Upvotes: 3

Tom
Tom

Reputation: 51566

Just use a data step.

%let y = jan feb mar apr may jun jul aug sep oct nov dec ;
data want;
  set &y ;
run;

Upvotes: 3

Related Questions