The SAS Output Delivery System and reporting techniques

Multiple Match Merging?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 220
Accepted Solution

Multiple Match Merging?

Suppose I have two datasets with dates as follows:

Dates1:

SubjectDate1
00129AUG2013
00221MAY2013
00204JUL2013
00220SEP2013
00317JUN2013
00416DEC2013

Dates2:

SubjectDate2
00108May2013
00125JUN2013
00113AUG2013
00217JAN2013
00209APR2013
00221MAY2013
00324JAN2013
00318MAR2013
00317JUN2013
00417JUN2013
00427JUN2013
00419AUG2013
00430SEP2013
00411NOV2013

I need to create a new dataset that compares DATES1 and DATES2.  If any of the dates in DATES1 are found in DATES2 for a given subject, then I want to flag that subject.  In other words, for the above example, my output would look like this:

SubjectMatch
0010
0021
0031
0040

I have been playing with this for a couple of days now, and I can't seem to find a solution.  What would be the best way to merge these two datasets so I can compare Date1 and Date2?  My main obstacle is the fact that there can be multiple entries for a single subject in both datasets, and I need to compare all dates in both datasets for a match.  Any ideas?


Accepted Solutions
Solution
‎01-16-2014 04:13 PM
Respected Advisor
Posts: 4,649

Re: Multiple Match Merging?

you could use this:

data date1;
input sub date :anydtdte.;
datalines;
001 29AUG2013
002 21MAY2013
002 04JUL2013
002 20SEP2013
003 17JUN2013
004 16DEC2013
;

data date2;
input sub date :anydtdte.;
datalines;
001 08May2013
001 25JUN2013
001 13AUG2013
002 17JAN2013
002 09APR2013
002 21MAY2013
003 24JAN2013
003 18MAR2013
003 17JUN2013
004 17JUN2013
004 27JUN2013
004 19AUG2013
004 30SEP2013
004 11NOV2013
;

proc sql;
create table subCount as
select sub, sum(date in (select date from date2 where sub=d1.sub)) as match
from date1 as d1
group by sub;
select * from subCount;
quit;

PG

PG

View solution in original post


All Replies
Solution
‎01-16-2014 04:13 PM
Respected Advisor
Posts: 4,649

Re: Multiple Match Merging?

you could use this:

data date1;
input sub date :anydtdte.;
datalines;
001 29AUG2013
002 21MAY2013
002 04JUL2013
002 20SEP2013
003 17JUN2013
004 16DEC2013
;

data date2;
input sub date :anydtdte.;
datalines;
001 08May2013
001 25JUN2013
001 13AUG2013
002 17JAN2013
002 09APR2013
002 21MAY2013
003 24JAN2013
003 18MAR2013
003 17JUN2013
004 17JUN2013
004 27JUN2013
004 19AUG2013
004 30SEP2013
004 11NOV2013
;

proc sql;
create table subCount as
select sub, sum(date in (select date from date2 where sub=d1.sub)) as match
from date1 as d1
group by sub;
select * from subCount;
quit;

PG

PG
Regular Contributor
Posts: 220

Re: Multiple Match Merging?

I never would have thought of that on my own.  Thank you so much!

Respected Advisor
Posts: 4,649

Re: Multiple Match Merging?

You are welcome. You can reverse the role of date1 and date2 to get the match counts of table2 subjects. - PG

PG
Super User
Posts: 5,082

Re: Multiple Match Merging?

Call me old-fashioned ??  I probably would use a DATA step instead of SQL:

data want;

   merge date1 (in=in1) date2 (in=in2 rename=(date2=date1));

   by subject date1;

   if first.subject then match=0;

   if in1 and in2 then match=1;

   retain match;

   keep subject match;

   if last.subject;

run;


Respected Advisor
Posts: 4,649

Re: Multiple Match Merging?

FAIW - Replace match=1 with match+1 to get the count of matching dates for each subject and render the retain statement redundant. - PG

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 455 views
  • 1 like
  • 3 in conversation