Output the result of a join

[GN0001]

Hello team,

I am doing an inner join. 

I have one left table and 4 right tables,

I need to do inner joins with these 4 tables.

left table with first right table then output

left table with second right table then output

until the end,

but each time I do the joins, the second join should be with the rest of observations that

were not joined in the first attempt.

I hope this makes sense, if not, please let me know.

Respectfully,

blue

[SASKiwi]

If I understand your requirements correctly and assume that your 4 right tables have the same columns then something like this should work:

proc sql;
  create table want1 as

  select '1' as JoinNum
        ,*
  from Table1 as T1
  inner join table2 as T2
  on T1.key = T2.key

  union

  select '2' as JoinNum
        ,*
  from Table1 as T1
  inner join table3 as T2
  on T1.key = T2.key

  union

  select '3' as JoinNum
        ,*
  from Table1 as T1
  inner join table4 as T2
  on T1.key = T2.key

  union

  select '4' as JoinNum
        ,*
  from Table1 as T1
  inner join table5 as T2
  on T1.key = T2.key

  order by key, JoinNum
  ;
quit;

data want2;
  set want1;
  by key JoinNum;
  if first.key;
run;

The strategy is to do all 4 joins and to give each a priority. By sorting the 4 joins in priority order, you can ensure only the highest priority join is chosen.