topic Re: Selecting an ID when data spans multiple rows in SAS Programming

Selecting an ID when data spans multiple rows

thockaday — Tue, 01 Jun 2021 19:43:09 GMT

Here is my data:

ID_Number	year	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
1	2018	0	0	0	1	1	0	0	0	0	0	0	1
1	2019	1	1	1	1	1	1	1	1	1	1	1	1
2	2018	1	1	0	0	1	1	1	1	1	1	1	1
2	2019	1	1	1	1	1	1	0	0	0	0	0	0

An ID qualifies to be selected if they were a member 90 days before and after an event.

If both ID's had an event in Feb 2019 (20190215) which ID number(s) would be selected?

In response to a question - there is an event table containing ID and Event_date YYYYMMDD.

thank you

Note: (I would expect only ID 2 would be selected)

Re: Selecting an ID when data spans multiple rows

ballardw — Tue, 01 Jun 2021 19:25:55 GMT

So, how do we know when an "event" occurs???

Re: Selecting an ID when data spans multiple rows

thockaday — Tue, 01 Jun 2021 19:32:08 GMT

I was just trying to keep it simple as possible, i'm trying to figure out how to process the critera when it spans multiple records - maybe even considering the possibility of flattening the data - IE "ID, "Year/Month", "Flag".
To answer your question, there is an "Event Table" containing "ID" and "Event date".

Re: Selecting an ID when data spans multiple rows

Kurt_Bremser — Tue, 01 Jun 2021 19:56:18 GMT

First of all, make your data intelligent by transposing to a long format, and making SAS dates.

Then you can use a simple join to find your answer.

data long;
set have;
array m {12} jan--dec;
do month = 1 to 12;
  if m{month)
  then do;
    mem_month = mdy(month,1,year);
    output;
  end;
end;
keep id_number mem_month;
run;

data want;
merge
  long
  have_event
;
by id_number;
if event_date - mem_month le 90 then output;
run;

Re: Selecting an ID when data spans multiple rows

thockaday — Wed, 02 Jun 2021 01:44:26 GMT

Hi Kurt,
Thank you for your answer, but I am not getting the desired result.
The long output does not include the 1 or 0 member flag ( 1=Yes and 0=No).
On the data Want, I need to select the ID if a person was a member for the 90 days BEFORE the event AND 90 days AFTER the event.
So if the event was 2019/02/15 (feb 15th) then the person would have to have been a member for the following 7 months : Nov, Dec 2018 and Jan-May 2019.

Re: Selecting an ID when data spans multiple rows

Kurt_Bremser — Wed, 02 Jun 2021 08:06:31 GMT

So instead of just finding one observation in the span, we need to count if 7 observations can be found.

An we need to take care of 3-month intervals, which are usually not 90 days.

See this complete and tested code:

data have;
input ID_Number $ year jan feb mar apr may jun jul aug sep oct nov dec;
datalines;
1 2018  0 0 0 1 1 0 0 0 0 0 0 1
1 2019  1 1 1 1 1 1 1 1 1 1 1 1
2 2018  1 1 0 0 1 1 1 1 1 1 1 1
2 2019  1 1 1 1 1 1 0 0 0 0 0 0
;

data have_event;
input id_number $ event_date :yymmdd8.;
format event_date yymmdd10.;
datalines;
1 20190215
2 20190215
;

data long;
set have;
array m {12} jan--dec;
do month = 1 to 12;
  if m{month}
  then do;
    mem_month = mdy(month,1,year);
    output;
  end;
end;
format mem_month yymmdd10.;
keep id_number mem_month;
run;

data want;
merge
  long
  have_event
;
by id_number;
if first.id_number then counter = 0;
if intnx('month',event_date,-3,"b") le mem_month le intnx('month',event_date,3,'b') then counter + 1;
if last.id_number;
if counter ge 7
then flag = 1;
else flag = 0;
keep id_number flag;
run;

proc print data=want noobs;
run;

Result:

ID_Number	flag
1	0
2	1

If you comment the KEEP statement, you will see that the counter is 6 and 7, respectively.

Re: Selecting an ID when data spans multiple rows

thockaday — Wed, 02 Jun 2021 15:01:38 GMT

Hi Kurt,
1. What in the code tells SAS to keep only months with a '1' flag? just wondering.
2. to determine the 'counter' I would need to:
A. first take the event date and subtract 90 days and determine what month that was
so in the example 2/15/19 - 90 = 11/17/18 so (November 2018)
B. Second add 90 days to the event date and determine that month
2/15/19 + 90 = 5/16/19 (May 2019)

in this example the range is Nov 2018 to May 2019 (7 months)
based on when the event date happens, it might not always be 7 months
for example if the event date was 3/1/19 then the range is 12/01/18 - 5/30/19 (6 months)
so i have to calculate the number of months from the resulting date range.

thanks for your help with this!

Re: Selecting an ID when data spans multiple rows

Kurt_Bremser — Wed, 02 Jun 2021 15:11:24 GMT

This filters the 1s:

if m{month}

A zero causes this to be false.

Since your membership table has only months, the precise dates become irrelevant, so I align everything to the first of a month, first when transposing the dataset, and next when calculating the time window with INTNX.

Re: Selecting an ID when data spans multiple rows

thockaday — Wed, 02 Jun 2021 16:45:32 GMT

Awesome - thank you!