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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

 

 

View solution in original post

3 REPLIES 3
Shmuel
Garnet | Level 18

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;
Kurt_Bremser
Super User

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 964 views
  • 0 likes
  • 3 in conversation