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
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.
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.