Comparing data between 4 data sets and join

[kajal_30]

Hi all,

I have 4 datasets as below and requirement for the table want are :

- Ignore col1 values which are not common for all 4 tables

- join all col1 col2 col3 col4 values in the want table to show all different values for col2 in each table.

Table 1
col1	col2
1	yes
2	yes
3	yes
4	yes
5

Table 2
col1	col2
1	yes
2	no1
3	yes
4	no1

Table 3
col1	col2
1	yes
2	no2
3	yes
4	no2

Table 4
col1	col2
1	yes
2	no3
3	yes
4	no3

table want
col1	col2	col3	col4	col5
1	yes	yes	yes	yes
2	yes	no1	no2	no3
3	yes	yes	yes	yes
4	yes	no1	no2	no3

Thanks in advance

kajal

[ChrisNZ · Accepted solution]

Like this?

data WANT;
  merge T1(in=IN1)
        T2(in=IN2 rename=(COL2=COL3)) 
        T3(in=IN3 rename=(COL2=COL4))
        T4(in=IN4 rename=(COL2=COL5));
  by COL1;      
  if IN1 & IN2 & IN3 & IN4;
run;

[kajal_30]

Thank you

[Kurt_Bremser]

Just for giggles:

proc sql;
create table want as
  select
    t1.col1,
    t1.col2,
    t2.col2 as col3,
    t3.col2 as col4,
    t4.col2 as col5
  from table1 t1, table2 t2, table3 t3, table4 t4
  where
    t1.col1 = t2.col1 and
    t1.col1 = t3.col1 and
    t1.col1 = t4.col1
;
quit;

[kajal_30]

Thank you