BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Alexxxxxxx
Pyrite | Level 9

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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;

Patrick_0-1596278920033.png

 

View solution in original post

3 REPLIES 3
Alexxxxxxx
Pyrite | Level 9
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
Opal | Level 21

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;

Patrick_0-1596278920033.png

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 441 views
  • 0 likes
  • 3 in conversation