BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
djbateman
Lapis Lazuli | Level 10

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?

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

5 REPLIES 5
PGStats
Opal | Level 21

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
djbateman
Lapis Lazuli | Level 10

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

PGStats
Opal | Level 21

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

PG
Astounding
PROC Star

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;


PGStats
Opal | Level 21

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

sas-innovate-2024.png

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.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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