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

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

tlnarayana26 — Mon, 13 Nov 2017 12:11:10 GMT

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.

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

Shmuel — Mon, 13 Nov 2017 12:01:01 GMT

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;

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

Kurt_Bremser — Mon, 13 Nov 2017 12:39:54 GMT

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

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

tlnarayana26 — Tue, 14 Nov 2017 07:06:41 GMT

Thanks