CODEWITHSUNDEEP
sassas-macro
Eder Domingues
Eder Domingues

Reputation: 57

Iterating rows of a query creating new tables

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.

Upvotes: 1

Views: 275

Answers (3)

Tom
Tom

Reputation: 51621

A normal pattern for this is to make a macro that takes the table name as a parameter.

%MACRO MAKE_TABLE(TABLE_NAME);
...
  FROM WORK.&TABLE_NAME
...
%MEND MAKE_TABLE;

Then you can use CALL EXECUTE to generate one call to the macro for each observation in your LIST table.

data _null_;
   set list;
   call execute(cats('%nrstr(%make_table)(',table_name,')'));
run;

Adding %nrstr() around the %make_table will make sure that the macro call itself is pushed onto the stack to run after the data step instead of code that it generates. This will make the log easier to read. It will also prevent timing issues when the macro has logic that depends on evaluating the results of executing the code the macro generates.

Upvotes: 2

Richard
Richard

Reputation: 27518

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

Upvotes: 1

Eder Domingues
Eder Domingues

Reputation: 57

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 believe it is not the most effective method.

However, this method depends on an ID in the LIST table.

Upvotes: 0

Related Questions