merge several datasets based on following situations

[Alexxxxxxx]

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

1. For ID 1, the SP_ID is ‘a’ in both table 1 and table 2.
2. For ID 2, non-table has corresponding SP_ID.
3. For ID 3, SP_ID are ‘c’ in table1 and ‘m’ in table2.
4. For ID 4, it has two corresponding SP_ID (ie., ‘f’,’h’) in both table 1 and table 2.

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;

[Kurt_Bremser]

From where do you get ID 2? It is not present in any table.

[Alexxxxxxx]

hello Kurt,

Thanks for your reply. Please ignore ID 2.

There is an original dataset which contains a list of ID, some of them are merged with SP_ID based on a variable in another dataset (eg, ID 1,3,4,5,6), some are not (eg.,2).

[Patrick · Accepted solution]

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;