Iterating rows of a query creating new tables

Question

I have a table containing the names of several other tables in a project.

As the LIST table below:

DATA WORK.LIST; INPUT TABLE_ID TABLE_NAME : $CHAR25.;
DATALINES;
1 CUSTOMERS
2 PRODUCTS
3 ORDERS
;

DATA WORK.CUSTOMERS; INPUT CUSTOMER_ID CUSTOMER_NAME $;
DATALINES;
1 David
2 Jose
3 Marcos
4 Josue
;

DATA WORK.PRODUCTS; INPUT PRODUCT_ID PRODUCT_NAME  $;
DATALINES;
41574 Tevision
35741 Refrigerator
74585 Cooker
;

DATA WORK.ORDERS; INPUT ORDER_ID CUSTOMER_ID PRODUCT_ID;
DATALINES;
741 1 41574
987 4 74585
888 4 35741
111 2 41574
;

I need to perform a certain processing, through a query, in all the tables of this project.

So I wrote a macro that executes the queries by changing the name of the tables.

PROC SQL NOPRINT; SELECT COUNT(*) INTO : NUM  FROM WORK.INICIO; QUIT;

%MACRO MAKE_TABLE;
    %DO i = 1 %TO #
        PROC SQL NOPRINT;
            SELECT TABLE_NAME INTO : VAR_TABLE_NAME
            FROM WORK.LIST
            WHERE TABLE_ID = &i.;
        QUIT;
        PROC SQL;
            CREATE TABLE TABLE_&i AS
            SELECT *
            FROM WORK.&VAR_TABLE_NAME;
        QUIT;
    %END;
%MEND;

%MAKE_TABLE;

It works, but I think it is not the most effective method.

Answer 1

Select the table names into a macro variable that can be parsed for each name, which in turn is used in further code generation.

Example:

%macro do_same_query_each_table;

  proc sql noprint;
    select table_name into :names separated by ' ' from work.list;
  quit;

  %local i table_name;
  %do i = 1 %to &SQLOBS;

    %let table_name = %scan(&names,&i);

    proc sql;
      ...query here...
        ... from &table_name ...
      ...query here...
    quit;

  %end;

%mend;

%do_same_query_each_table