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
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.