SAS Macro - Combining multiple tables into one, controlled by another table

Question

I've come in late to a project and want to write a macro that normalises some data for export to a SQL Server.

There are two control tables...
- Table 1 (customers) has a list of customer unique identifiers
- Table 2 (hierarchy) has a list of table names

There are then n additional tables. One for each record in (hierarchy) (named in the SourceTableName field). With the form of...
- CustomerURN, Value1, Value2

I want to combine all of these tables into a single table (sample_results), with the form of...
- SourceTableName, CustomerURN, Value1, Value2

The only records that should be copied, however, should be for CustomerURNs that exist in the (customers) table.

I could do this in a hard coded format using proc sql, something like...

proc sql;
insert into
  SAMPLE_RESULTS
select
  'TABLE1',
  data.*
from 
  Table1    data
INNER JOIN
  customers
    ON data.CustomerURN = customers.CustomerURN

<repeat for every table>

But every week new records are added to the hierarchy table.

Is there any way to write a loop that picks up the table name from the hierarchy table, then calls the proc sql to copy the data into sample_results?

Answer 1

You could concatenate all the hierarchy tables together, and do a single SQL join

proc sql ;
  drop table all_hier_tables ;
quit ;

    %MACRO FLAG_APPEND(DSN) ;
      /* Create new var with tablename */
      data &DSN._b ;
        length SourceTableName $32. ;
        SourceTableName = "&DSN" ;
        set &DSN ;
      run ;

      /* Append to master */
      proc append data=&DSN._b base=all_hier_tables force ; 
      run ;
    %MEND ;

    /* Append all hierarchy tables together */
    data _null_ ;
      set hierarchy ;
      code = cats('%FLAG_APPEND(' , SourceTableName , ');') ;
      call execute(code); /* run the macro */
    run ;

    /* Now merge in... */
    proc sql;
    insert into
      SAMPLE_RESULTS
    select
      data.*
    from 
      all_hier_tables data
    INNER JOIN
      customers
        ON data.CustomerURN = customers.CustomerURN
quit;