BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
thockaday
Calcite | Level 5

Here is my data:

ID_Numberyearjanfebmaraprmayjunjulaugsepoctnovdec
12018000110000001
12019111111111111
22018110011111111
22019111111000000

 

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

8 REPLIES 8
ballardw
Super User

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

thockaday
Calcite | Level 5
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".
Kurt_Bremser
Super User

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;
thockaday
Calcite | Level 5
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.
Kurt_Bremser
Super User

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.

thockaday
Calcite | Level 5
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!
Kurt_Bremser
Super User

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.

thockaday
Calcite | Level 5
Awesome - thank you!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1476 views
  • 0 likes
  • 3 in conversation