Dear all,
How to merge six datasets based on following conditions?
I have six datasets, each of them contains three volumes, ID, SP_ID, Flag_&no.. The Flag_&no. equals 1 if ID has a corresponding SP_ID on the same line. There are sample of four tables here. And I would like to merge them together.
Tabl1 | ||
ID | SP_ID | Flag_1 |
1 | a | 1 |
3 | c | 1 |
4 | f | 1 |
4 | h | 1 |
Table2 | ||
ID | SP_ID | Flag_2 |
1 | a | 1 |
3 | m | 1 |
4 | h | 1 |
4 | f | 1 |
Table3 | ||
ID | SP_ID | Flag_3 |
5 | n | 1 |
Table4 | ||
ID | SP_ID | Flag_4 |
6 | q | 1 |
I would like to get the table like below
want |
|
|
|
|
|
ID | SP_ID | Flag_1 | Flag_2 | Flag_3 | Flag_4 |
1 | a | 1 | 1 | ||
2 | |||||
3 | c | 1 | |||
3 | m | 1 | |||
4 | f | 1 | 1 | ||
4 | h | 1 | 1 | ||
5 | n | 1 | |||
6 | q | 1 |
Please note the following situations in the table
Do you know what should I do?
data table1; infile cards dsd dlm=","; input ID $ SP_ID $ Flag_1 $ ; cards; 1,a,1 3,c,1 4,f,1 4,h,1 ;;;; run; data table2; infile cards dsd dlm=","; input ID $ SP_ID $ Flag_1 $ ; cards; 1,a,1 3,m,1 4,h,1 4,f,1 ;;;; run; data table3; infile cards dsd dlm=","; input ID $ SP_ID $ Flag_1 $ ; cards; 5,n,1 ;;;; run; data table4; infile cards dsd dlm=","; input ID $ SP_ID $ Flag_1 $ ; cards; 6,q,1 ;;;; run;
Here you go.
data table1;
infile cards dsd dlm=",";
input
ID $
SP_ID $
Flag_1 $
;
cards;
1,a,1
3,c,1
4,f,1
4,h,1
;
data table2;
infile cards dsd dlm=",";
input
ID $
SP_ID $
Flag_1 $
;
cards;
1,a,1
3,m,1
4,h,1
4,f,1
;
data table3;
infile cards dsd dlm=",";
input
ID $
SP_ID $
Flag_1 $
;
cards;
5,n,1
;
data table4;
infile cards dsd dlm=",";
input
ID $
SP_ID $
Flag_1 $
;
cards;
6,q,1
;
proc sort data=table1;
by id sp_id;
run;
proc sort data=table2;
by id sp_id;
run;
proc sort data=table3;
by id sp_id;
run;
proc sort data=table4;
by id sp_id;
run;
data want;
merge
table1
table2(rename=(flag_1=flag_2))
table3(rename=(flag_1=flag_3))
table4(rename=(flag_1=flag_4))
;
by id sp_id;
run;
proc print data=want;
run;
From where do you get ID 2? It is not present in any table.
Here you go.
data table1;
infile cards dsd dlm=",";
input
ID $
SP_ID $
Flag_1 $
;
cards;
1,a,1
3,c,1
4,f,1
4,h,1
;
data table2;
infile cards dsd dlm=",";
input
ID $
SP_ID $
Flag_1 $
;
cards;
1,a,1
3,m,1
4,h,1
4,f,1
;
data table3;
infile cards dsd dlm=",";
input
ID $
SP_ID $
Flag_1 $
;
cards;
5,n,1
;
data table4;
infile cards dsd dlm=",";
input
ID $
SP_ID $
Flag_1 $
;
cards;
6,q,1
;
proc sort data=table1;
by id sp_id;
run;
proc sort data=table2;
by id sp_id;
run;
proc sort data=table3;
by id sp_id;
run;
proc sort data=table4;
by id sp_id;
run;
data want;
merge
table1
table2(rename=(flag_1=flag_2))
table3(rename=(flag_1=flag_3))
table4(rename=(flag_1=flag_4))
;
by id sp_id;
run;
proc print data=want;
run;
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.