last 12 months of data for members where each start date is different

[jmmedina25]

Hello,  I'm looking to get the last 12 months of data for a list of members.  The 12 month lookback period needs to start on the person's date of death for each member.  Does any one know how I could do this?

Example data:

Member#      Death_date

    1               1/1/2020

    2               2/1/2020

    3               3/1/2020

second member data table:

member#      activity_date

    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

last table after sorting to last 12 months of persons life:

member#      activity_date

    1                 12/1/2019

    1                 10/1/2019

    2                 3/1/2019

    2                 8/1/2019

    3                 12/1/2019

[ballardw · Accepted solution]

Please provide example data in the form of data step code and preferably pasted into a text box or code box opened on the forum with the </> or running man icons.

Two ways:

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;


proc sql;
   create table want as
   select b.member, b.activity_date
   from one as a 
        left join
        two as b
        on a.member=b.member
   where b.activity_date ge intnx('month',a.death_date,-12,'s');
quit;

/* data step merge requires both sets to sort by member*/
data want2;
   merge one two;
   by member;
   if activity_date ge intnx('month',death_date,-12,'s');
run;

Which one to use may be dependent on other needs.

The basic bit is to 1) make sure you have SAS date values and 2) the INTNX function is used to adjust date, time or datetime periods such as "12 months prior".

[acordes]

use the intck function in a join condition for a proc sql

[mkeintz]

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;