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

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;

mmm7_0-1610646559498.png

 

Here is an example of how I would like my data set...

mmm7_1-1610646614867.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20

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
ballardw
Super User

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.

mmm7
Calcite | Level 5

Thank you @novinosrin @ballardw for your quick solutions! worked like a charm. 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1262 views
  • 0 likes
  • 3 in conversation