topic Re: merging? in SAS Programming

merging?

mmm7 — Thu, 14 Jan 2021 17:53:53 GMT

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

Re: merging?

novinosrin — Thu, 14 Jan 2021 18:15:27 GMT

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

Re: merging?

ballardw — Thu, 14 Jan 2021 18:17:09 GMT

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.

Re: merging?

mmm7 — Thu, 14 Jan 2021 18:29:58 GMT

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