SAS Dynamic SQL join

Question

I want to join 6 tables, which all have different variables, to one table, which has same columns as all 6 other tables. Can i somehow do it without looking at these tables and watching which columns these tables have? I have got macro variable, an array, with column names, but I cannot think of any good way how to join these tables using this array.

Array is created by this macro:

%macro getvars(dsn);
%global vlist;
proc sql noprint;
select name into :vlist separated by ' '
from dictionary.columns
where memname=upcase("&dsn");
quit;
%mend getvars;

And i want to just join tables like this:

proc sql;
create table new_table as select * from table1 as l 
left join table2 as r on l.age=r.age and l.type=r.type;
quit;

but not so manually :)

For example, table1 has columns name, age, coef1 and sex, table 2 has columns name, region and coef2. The third table, where I want to join them has name, age, sex, region, coef and many other columns. I want to write a program, that doesn't know which table has which columns, but joins so that third table still has all the same columns plus coef1 and coef2.

Answer 1

This isn't an answer I'd normally recommend as it can lead to unwanted results if you're not careful, however it could work for you in this instance. I'm proposing using a natural join, which automatically joins on to all matching variables so you don't need to specify an ON clause. Here's example code.

proc sql;
create table want as select
*
from 
    a
  natural left join 
    b
  natural left join 
    c
;
quit;

As I say, be very careful about checking the results

Answer 2

Here's one method...

Firstly, use DICTIONARY.COLUMNS to find all of the common variables in each table based on the 'master' table. Then dynamically generate the join criteria for tables with common variables, and finally join them all together based on those criteria.

%MACRO COMMONJOIN(DSN,DSNLIST) ;
  %LET DSNC = %SYSFUNC(countw(&DSNLIST,%STR( ))) ; /* # of additional tables */

  /* Create a list of variables from primary DSN, with flags where variable exists in DSNLIST datasets */
  proc sql ;
    create table commonvars as
    select a.name %DO I = 1 %TO &DSNC ;
                    %LET D = %SYSFUNC(scan(&DSNLIST,&I,%STR( ))) ;
                 , d&I..V&I label="&D"
                  %END ;

    from dictionary.columns a
         %DO I = 1 %TO &DSNC ;
           /* Iterate over list of dataset names */
           %LET D = %SYSFUNC(scan(&DSNLIST,&I,%STR( ))) ;

           left join
           (select name, 1 as V&I
            from dictionary.columns
            where libname = scan(upcase("&D"),1,'.')
              and memname = scan(upcase("&D"),2,'.'))
              as d&I on a.name = d&I..name
         %END ;
    where libname = scan(upcase("&DSN"),1,'.')
      and memname = scan(upcase("&DSN"),2,'.')
    ;
  quit ;

  /* Create join criteria between master & each secondary table */
  %DO I = 1 %TO &DSNC ;
    %LET JOIN&I = ;
    proc sql ;
      select catx(' = ',cats('a.',name),cats("V&I..",name)) into :JOIN&I separated by ' and '
      from commonvars
      where V&I = 1 ;
    quit ;
  %END ;

  /* Join */
  proc sql ;
    create table masterjoin as
    select a.*
           %DO I = 1 %TO &DSNC ;
             %IF "&&JOIN&I" ne "" %THEN %DO ;
         , V&I..*
             %END ;
           %END ;
    from &DSN as a
         %DO I = 1 %TO &DSNC ;
           %IF "&&JOIN&I" ne "" %THEN %DO ;
             %LET D = %SYSFUNC(scan(&DSNLIST,&I,%STR( ))) ;
         left join &D as V&I on &&JOIN&I
           %END ;
         %END ;
    ;
  quit ;

%MEND ;

%COMMONJOIN(work.master,work.table1 work.table2 work.table3) ;

Answer 3

If you are open to using a data step instead of proc sql you may be in luck.

/* pre-sorting is required for SAS merge */    
proc sort data=master; by key1 key2; run;

proc sort data=table1; by key1 key2; run;
proc sort data=table2; by key1 key2; run;
proc sort data=table3; by key1 key2; run;

data want;
merge master (in=_inMaster) table1 table2 table3;
by key1 key2;

/* for a Left Join, keep all rows from Master */
if _inMaster;
run;

The only gotcha I can think of is a common variable name among the non-key fields. If more than one table has variable x, the right-most table's value of x will overwrite the previous ones, but SAS will note this in the log.