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.
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
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;
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
Thanks
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.