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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: