DATA Step, Macro, Functions and more

I was trying to merge data only where exists in both datasets.

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

I was trying to merge data only where exists in both datasets.

[ Edited ]

I have two tables TAB1 and TAB2,  Both have two common variables followed by other different variables. so what i need here is

When we join both the tables i need all the records from both the tables. But the condition here is if both the table have common variables it has to merge the data else no.

 

Tab1 :                                                                                                                                

Date Member Code Amount
4/1/2017 50000 ABC 5
4/1/2017 50000 DEF 7
4/1/2017 50000 XYZ 4
3/1/2017   PQR 6
3/1/2017 6000 EFX  
3/1/2017 70000 VXB 5
3/1/2017 70000 IDJ 9
       

 

Tab2

Date Member Age Name
4/1/2017 50000 43 JAMES SCHMIDT
4/1/2017 50000 54 JACK BALDWIN
4/1/2017 50000 32 DAVID OPACK
4/1/2017 50000 23 HEATHER MARKS
4/1/2017 50000 49 DANIEL LUNSTAD
3/1/2017 70000   JACK BALDWIN
3/1/2017 70000 46 JAMES SCHMIDT
3/1/2017 5222 75 DANIEL LUNSTAD

 

Required output;

Date Member Code Amount Age Name
           
3/1/2017   PQR 6    
3/1/2017 5222     75 DANIEL LUNSTAD
3/1/2017 6000 EFX      
3/1/2017 70000 VXB 5   JACK BALDWIN
3/1/2017 70000 IDJ 9 46 JAMES SCHMIDT
4/1/2017 50000 ABC 5 43 JAMES SCHMIDT
4/1/2017 50000 DEF 7 54 JACK BALDWIN
4/1/2017 50000 XYZ 4 32 DAVID OPACK
4/1/2017 50000     23 HEATHER MARKS
4/1/2017 50000     49 DANIEL LUNSTAD

 

 

 

 

 

Thanks and regards,

Tlnarayana.


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 7,866

Re: I was trying to merge data only where exists in both datasets.

Posted in reply to tlnarayana26

Don' post Excel files for examples. Excel files do not convey important metadata needed to recreate datasets. Use a data step instead.

See this example:

data tab1;
input date :mmddyy10. member code $ amount;
format date mmddyy10.;
cards;
4/1/2017 	50000 	ABC 	5
4/1/2017 	50000 	DEF 	7
4/1/2017 	50000 	XYZ 	4
3/1/2017 	  	PQR 	6
3/1/2017 	6000 	EFX 	 
3/1/2017 	70000 	VXB 	5
3/1/2017 	70000 	IDJ 	9
;
run;

data tab2;
input date :mmddyy10. member age name $30.;
cards;
4/1/2017 	50000 	43 	JAMES SCHMIDT
4/1/2017 	50000 	54 	JACK BALDWIN
4/1/2017 	50000 	32 	DAVID OPACK
4/1/2017 	50000 	23 	HEATHER MARKS
4/1/2017 	50000 	49 	DANIEL LUNSTAD
3/1/2017 	70000 	  	JACK BALDWIN
3/1/2017 	70000 	46 	JAMES SCHMIDT
3/1/2017 	5222 	75 	DANIEL LUNSTAD
;
run;

proc sort data=tab1;
by date member;
run;

proc sort data=tab2;
by date member;
run;

data want;
if _n_ = 0 then merge tab1 tab2;
call missing (of _all_);
merge
  tab1
  tab2
;
by date member;
run;

proc print data=want noobs;
run;

Result of the data step merge:

      date    member    code    amount    age         name

03/01/2017         .    	PQR       6        .                   
03/01/2017      5222               .       75    	DANIEL LUNSTAD
03/01/2017      6000    	EFX       .        .                   
03/01/2017     70000    	VXB       5        .    	JACK BALDWIN 
03/01/2017     70000    	IDJ       9       46    	JAMES SCHMIDT
04/01/2017     50000    	ABC       5       43    	JAMES SCHMIDT
04/01/2017     50000    	DEF       7       54    	JACK BALDWIN 
04/01/2017     50000    	XYZ       4       32    	DAVID OPACK  
04/01/2017     50000               .       23    	HEATHER MARKS 
04/01/2017     50000               .       49    	DANIEL LUNSTAD

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Trusted Advisor
Posts: 1,586

Re: I was trying this data tab3;merge tab1 tab2;by comnvar; run;

Posted in reply to tlnarayana26

What does it mean that a memeber (5000 date=4q1q2017) has two different names and different age?

 

You cam join the two datasets using sql:

 

      

proc sql
       create table want as select a.* , b.*
        from tab1 as a
        inner join tab2 as b
        on a.date = b.date and a.member=b.member;
quit;
Solution
2 weeks ago
Super User
Posts: 7,866

Re: I was trying to merge data only where exists in both datasets.

Posted in reply to tlnarayana26

Don' post Excel files for examples. Excel files do not convey important metadata needed to recreate datasets. Use a data step instead.

See this example:

data tab1;
input date :mmddyy10. member code $ amount;
format date mmddyy10.;
cards;
4/1/2017 	50000 	ABC 	5
4/1/2017 	50000 	DEF 	7
4/1/2017 	50000 	XYZ 	4
3/1/2017 	  	PQR 	6
3/1/2017 	6000 	EFX 	 
3/1/2017 	70000 	VXB 	5
3/1/2017 	70000 	IDJ 	9
;
run;

data tab2;
input date :mmddyy10. member age name $30.;
cards;
4/1/2017 	50000 	43 	JAMES SCHMIDT
4/1/2017 	50000 	54 	JACK BALDWIN
4/1/2017 	50000 	32 	DAVID OPACK
4/1/2017 	50000 	23 	HEATHER MARKS
4/1/2017 	50000 	49 	DANIEL LUNSTAD
3/1/2017 	70000 	  	JACK BALDWIN
3/1/2017 	70000 	46 	JAMES SCHMIDT
3/1/2017 	5222 	75 	DANIEL LUNSTAD
;
run;

proc sort data=tab1;
by date member;
run;

proc sort data=tab2;
by date member;
run;

data want;
if _n_ = 0 then merge tab1 tab2;
call missing (of _all_);
merge
  tab1
  tab2
;
by date member;
run;

proc print data=want noobs;
run;

Result of the data step merge:

      date    member    code    amount    age         name

03/01/2017         .    	PQR       6        .                   
03/01/2017      5222               .       75    	DANIEL LUNSTAD
03/01/2017      6000    	EFX       .        .                   
03/01/2017     70000    	VXB       5        .    	JACK BALDWIN 
03/01/2017     70000    	IDJ       9       46    	JAMES SCHMIDT
04/01/2017     50000    	ABC       5       43    	JAMES SCHMIDT
04/01/2017     50000    	DEF       7       54    	JACK BALDWIN 
04/01/2017     50000    	XYZ       4       32    	DAVID OPACK  
04/01/2017     50000               .       23    	HEATHER MARKS 
04/01/2017     50000               .       49    	DANIEL LUNSTAD

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 42

Re: I was trying to merge data only where exists in both datasets.

Posted in reply to KurtBremser

Thanks

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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