If both datasets are sorted by ID, then the merge solution offered by @ballardw is likely the most efficient.
But if either of the datasets is not sorted, and sorting is expensive, then there is a single-data-step solution. It depends on storing the death_dates (and associated cutoff_dates) in a hash object h, prior to processing the activity dates.
data one;
input Member $ Death_date :mmddyy10.;
format death_date mmddyy10.;
datalines;
1 1/1/2020
2 2/1/2020
3 3/1/2020
;
data two;
input member $ activity_date :mmddyy10.;
format activity_date mmddyy10.;
datalines;
1 12/1/2019
1 10/1/2019
1 1/1/2017
2 3/1/2019
2 8/1/2019
2 1/1/2016
3 12/1/2019
3 2/1/2019
3 4/1/2016
run;
data want;
set one (in=inone) two (in=intwo); /*Read all death dates prior to activity dates */
if inone then cutoff_date=intnx('month',death_date,-12,'same');
format cutoff_date mmddyy10.;
if _n_=1 then do;
declare hash h ();
h.definekey('member');
h.definedata('cutoff_date','death_date');
h.definedone();
end;
if inone then h.add(); /*Incoming death date? Add it and the cutoff_date to hash object */
else h.find(); /*Otherwise retrieve this member's death_date and cutoff_date */
if intwo and activity_date >= cutoff_date;
run;
This relies on every member in dataset TWO being present in ONE - i.e. every member with activity having a death_date.
If there is a chance of activity for a person without a death date, then replace the last 4 statements above with the below (_rc=0 below means a successful h.find())..
if inone then h.add(); /*Incoming death date? Add it and the cutoff_date to hash object */
else _rc=h.find(); /*Otherwise retrieve this member's death_date and cutoff_date */
if intwo and _rc=0 and activity_date >= cutoff_date;
drop _rc;
run;
... View more
