I have two tables, first table contains ID and Date1. Second table contains ID and Date2. I would like to merge the tables so each unique Date1 is repeated across all of the Date2 rows, per same ID. Essentially, I would like all of the data in t2 repeated per each unique t1 Date1. Currently, when I merge by ID, the second unique Date1 is repeated.
data t1; input ID Date1; cards; 1 14686 1 17786 ; run; data t2; input ID Date2; cards; 1 13486 1 15627 1 17777 1 19827 ; format Date2 mmddyy10.; run; proc sort data = t1; by id; proc sort data = t2; by id; data merged_table; merge t1 t2; by id; format date: mmddyy10.; run;
Here is an example of how I would like my data set...
Hi @mmm7 For many to many, datastep Merge isn't great, rather you are better off using SQL JOIN
data t1;
input ID Date1;
format Date1 mmddyy10.;
cards;
1 14686
1 17786
;
run;
data t2;
input ID Date2;
format Date2 mmddyy10.;
cards;
1 13486
1 15627
1 17777
1 19827
;
run;
proc sql;
create table want as
select a.id,date1, date2
from t1 a left join
t2 b
on a.id=b.id
order by a.id,date1,date2;
quit;
ID | Date1 | Date2 |
---|---|---|
1 | 03/17/2000 | 12/03/1996 |
1 | 03/17/2000 | 10/14/2002 |
1 | 03/17/2000 | 09/02/2008 |
1 | 03/17/2000 | 04/14/2014 |
1 | 09/11/2008 | 12/03/1996 |
1 | 09/11/2008 | 10/14/2002 |
1 | 09/11/2008 | 09/02/2008 |
1 | 09/11/2008 | 04/14/2014 |
Hi @mmm7 For many to many, datastep Merge isn't great, rather you are better off using SQL JOIN
data t1;
input ID Date1;
format Date1 mmddyy10.;
cards;
1 14686
1 17786
;
run;
data t2;
input ID Date2;
format Date2 mmddyy10.;
cards;
1 13486
1 15627
1 17777
1 19827
;
run;
proc sql;
create table want as
select a.id,date1, date2
from t1 a left join
t2 b
on a.id=b.id
order by a.id,date1,date2;
quit;
ID | Date1 | Date2 |
---|---|---|
1 | 03/17/2000 | 12/03/1996 |
1 | 03/17/2000 | 10/14/2002 |
1 | 03/17/2000 | 09/02/2008 |
1 | 03/17/2000 | 04/14/2014 |
1 | 09/11/2008 | 12/03/1996 |
1 | 09/11/2008 | 10/14/2002 |
1 | 09/11/2008 | 09/02/2008 |
1 | 09/11/2008 | 04/14/2014 |
The data step merge does not handle many-to-many merges very well in general.
Proc SQL has more options for dealing with combining data on multiple variables/values.
This may get you started:
proc sql; create table want as select t1.id, t1.date1 format=mmddyy10.,t2.date2 format=mmddyy10. from t1 left join t2 on t1.id=t2.id order by t1.id, t1.date1 ; quit;
Proc SQL does not require the data to be sorted prior though performance may be quicker when pre-sorted.
Maybe. Lots of factors involved.
Thank you @novinosrin @ballardw for your quick solutions! worked like a charm.
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.