topic Re: Trying to create a new binary variable based on a rolling time window in SAS Programming

Trying to create a new binary variable based on a rolling time window

righcoastmike — Tue, 16 Apr 2019 14:21:12 GMT

Hi folks,

I have a dataset that looks like this:

data have;
input studyID mth_date ed_visits year ;
cards;
1 2011-04 0 2011
1 2011-05 1 2011
1 2012-01 3 2012
2 2011-03 4 2011
3 2011-04 2 2011
3 2011-06 0 2011
3 2013-08 2 2013
run;

And I'm trying to create a dataset that will tell me which of the study id's have 3 or more ed_visits within 12 months ( we'll call them repeat users). The trick is that I don't care if it's 3 hospitalizations in the same year, just that within any 12 month period they have a total of 3 or more hospitalizations. So Using the above dataset, and creating a binary variable for "repeat user" my output could look something like:

data want;
input studyID mth_date ed_visits year Repeat_user ;
cards;
1 2011-04 0 2011 1
1 2011-05 1 2011 1
1 2012-01 3 2012 1
2 2011-03 4 2011 1
3 2011-04 2 2011 0
3 2011-06 0 2011 0
3 2013-08 2 2013 0
run;

Study ID's 1 and 2 both have enough hospitalizations to qualify as repeat users (value of 1), but #3 never had more than 2 visits within a 12 month period, so they remain 0's.

Alternatively, a list of the study ID's that meet the "repeat user" criteria would also work.

Also, just FYI the mnth_date variable is currently not a SAS date, just a string (not my fault that's just how the data came).

In essence I need to be able to sort out the people who made 3 or more trips to the ED within 12 months over the course of my study period from those that haven't

Any thoughts would be much appreciated!

Thanks so much

Mike

Re: Trying to create a new binary variable based on a rolling time window

PaigeMiller — Tue, 16 Apr 2019 15:21:18 GMT

PROC EXPAND will do this, it can count the sum of the ed_visits over a rolling X month time period. Once you have the rolling sum, you can create a binary variable.

As a hint to help this work, you are going to need to make sure your mth_date is an actual SAS date value. (As it stands, your input statement will fail to read 2011-04, for example. Perhaps there is an informat that will allow mth_date to be read as an actual SAS date value). Once you straighten that out, you can add in zeros for months that don't appear in data set HAVE, so that PROC EXPAND can work on 12 consecutive months.

Re: Trying to create a new binary variable based on a rolling time window

novinosrin — Tue, 16 Apr 2019 15:54:37 GMT

Hi @righcoastmike See if this works. If my understanding is correct, proc sql is rather easier. I fancied a DOW Hash for FWIW.

data have;
input studyID mth_date $ ed_visits year ;
md=input(compress(mth_date,'-'),yymmn6.);
format md date9.;
cards;
1 2011-04 0 2011
1 2011-05 1 2011
1 2012-01 3 2012
2 2011-03 4 2011
3 2011-04 2 2011
3 2011-06 0 2011
3 2013-08 2 2013
;
run;


data want;
if _n_=1 then do;
	if 0 then set have;
   dcl hash H (dataset:'have',ordered: "A",multidata:'y') ;
   h.definekey  ("studyID") ;
   h.definedata ("md","ed_visits") ;
   h.definedone () ;
end;
_k=0;
do until(last.studyid);
set have(rename=(md=_md ed_visits=_ed_visits));
by studyID;
do rc=h.find() by 0 while(rc=0);
if md>_md and intck('month',_md,md)<=12 then k=sum(ed_visits,_ed_visits);
rc=h.find_next();
end;
_k+k;
repeat_user=first.studyid and last.studyid and _ed_visits>=3  or k>=3 ;
end;
do until(last.studyid);
set have;
by studyID;
output;
end;
keep studyid mth_date ed_visits year repeat_user;
run;

Re: Trying to create a new binary variable based on a rolling time window

ballardw — Tue, 16 Apr 2019 15:37:45 GMT

@PaigeMiller wrote:

PROC EXPAND will do this, it can count the sum of the ed_visits over a rolling X month time period. Once you have the rolling sum, you can create a binary variable.

As a hint to help this work, you are going to need to make sure your mth_date is an actual SAS date value. (As it stands, your input statement will fail to read 2011-04, for example. Perhaps there is an informat that will allow mth_date to be read as an actual SAS date value). Once you straighten that out, you can add in zeros for months that don't appear in data set HAVE, so that PROC EXPAND can work on 12 consecutive months.

The anydtdte informat will read those values as the first of the month:

data have;
input studyID mth_date :anydtdte10. ed_visits year ;
format mth_date date9.;
cards;
1 2011-04 0 2011
1 2011-05 1 2011
1 2012-01 3 2012
2 2011-03 4 2011
3 2011-04 2 2011
3 2011-06 0 2011
3 2013-08 2 2013
run;

Re: Trying to create a new binary variable based on a rolling time window

righcoastmike — Tue, 16 Apr 2019 17:37:37 GMT

Hi folks,

I'm still working on the main problem, but fyi here is the solution that worked for fixing the date variable...... (current format is a string that looks like '2011-04')

data want;
set have;
format date2 date9.;
date2 = input(mthdate,anydtdte10.);
run;

where date2 is the corrected date variable, and mthdate is the original string variable.

hoping it can help someone else in a similar situation down the line, will report back when I get the rest sorted out.

Thanks so much!

Mike

Re: Trying to create a new binary variable based on a rolling time window

righcoastmike — Wed, 17 Apr 2019 13:55:04 GMT

Thanks so much for this Novinosrin.

I've given the code a try, and it looks like it's running, but it's been chugging away for about ten hours now. Any thoughts on why that might be?

Mike

Re: Trying to create a new binary variable based on a rolling time window

novinosrin — Wed, 17 Apr 2019 13:59:12 GMT

How large is your dataset? I hope there isn't any memory constraints

I ran against your sample and I trust that's a representative one. Nonetheless, I can work on a SQL solution as a parallel alternative if needed. Keep us posted.

Re: Trying to create a new binary variable based on a rolling time window

righcoastmike — Wed, 17 Apr 2019 14:02:26 GMT

It's a biggie I'm afraid. (approx, 5.6 million records total for approx 104,000 individuals). The sample is representative of the data format, but clearly not the size. Re: memory constraints I'm checking with the administrator now. My guess is that's what the issue is. would Proc SQL help to take some of the load off?

Thanks so much for working through this with me. It's much appreciated.

Re: Trying to create a new binary variable based on a rolling time window

novinosrin — Wed, 17 Apr 2019 14:07:32 GMT

Just 5.6 million records ?

Jeez, that's tiny. I was thinking a few 100 millions. Are you saying it's been running for 10 hours?Hmm that can't be true.

Did you paste the code correctly checking word by word before you started executing?

Did you test on a smaller sample to begin with and see if that works?

Anyways, I will test against 100 million in a bit on my machine and respond here but I don't think that will have any issues here. Nonetheless, I will be right back with SQL solution shortly after a coffee

Re: Trying to create a new binary variable based on a rolling time window

PaigeMiller — Wed, 17 Apr 2019 14:20:47 GMT

Why use SQL or hash, when SAS has already programmed exactly what you need in PROC EXPAND?

(One possible reason is that your SAS license does not contain PROC EXPAND, but this hasn't been stated by the OP)

Re: Trying to create a new binary variable based on a rolling time window

novinosrin — Wed, 17 Apr 2019 14:23:43 GMT

Sir @PaigeMiller Do you ever doubt I would disagree to your wise advice. If yes, change that. 🙂

Ok, I haven't used ETS much so I don't know much about it. 2nd, I am attempting with what I know. When all we have is a hammer, everything looks like a nail.

Re: Trying to create a new binary variable based on a rolling time window

novinosrin — Wed, 17 Apr 2019 15:11:50 GMT

Hi @righcoastmike here is a SQL solution. I have added some comments so that you can follow the logic/process. Test with a smaller sample, if things go well , scale up.


/*Your Original sample HAVE*/
data have;
input studyID mth_date $ ed_visits year ;
cards;
1 2011-04 0 2011
1 2011-05 1 2011
1 2012-01 3 2012
2 2011-03 4 2011
3 2011-04 2 2011
3 2011-06 0 2011
3 2013-08 2 2013
;
run;

/*Creating a temp dataset where we extract date numeric values from your char dates in the form
2011-04*/
/*This md=input(compress(mth_date,'-'),yymmn6.) does that */
data temp;
set have;
md=input(compress(mth_date,'-'),yymmn6.);
run;

/*Taking the previous temp table for our look ahead process to get our final want*/

proc sql;
create table want(drop=md) as
select distinct studyID,mth_date ,md, ed_visits, year, sum(t)>=3 as repeat_user
from
(select a.*,ifn(count(a.studyid)=1, a.ed_visits, (intck('month',a.md,b.md)<=12)*(b.ed_visits+a.ed_visits)) as t
from temp a left join temp b
on a.studyid=b.studyid and b.md>a.md
group by a.studyid)
group by studyid
order by studyid,md;
quit;

I used some boolean expressions. In essence the 2 select passes can still be made into one with more boolean games, however my concern is I want us to make it work to achieve a working solution first and once that's done, I will have some fun later.

Re: Trying to create a new binary variable based on a rolling time window

righcoastmike — Wed, 17 Apr 2019 18:38:30 GMT

Hi @novinosrin

Ok here's the update, i have managed to get the hash version mostly working, but it's missing some people. From what I can tell it looks as if there are a series of 1's and 2's in "ed_visits" it adds them up just fine and makes the appropriate flag. If there is a record with more than 2 it seems to miss it. For example, if someone has 2 visits in 2011-04 and 2 visits in 2011-05 the code works great. If however, the person has 3 visits in 2011-04 (which still counts as a repeat user) it misses them.

I'm still working on getting the SQL up and running, I have a working code but I think I might have messed something up because it's doing the opposite of the hash, it's catching way more than it should.

Any thoughts on either of those would be much appreciated.

Thanks so much

Mike

Re: Trying to create a new binary variable based on a rolling time window

righcoastmike — Wed, 17 Apr 2019 19:14:35 GMT

Hi @PaigeMiller

Thanks so much for this suggestion. I've managed to get the date value to where it needs to be, and I've been playing with proc expand.

The problem that I'm running into is that proc expand apparently doesn't like it when you have 2 identical id variables (time variables) right after each other and throws up an error.

Since I have multiple records taken from different subjects on the same date, I can't figure out how to make it work.

proc sort data=have;
by date;
run; 

proc expand data=have;
out=want
to=month
method=none;
id date;
run;

This gives me an "observation with duplicate ID value found" error.

Any thoughts would be much appreciated.

Thanks so much

Mike

Re: Trying to create a new binary variable based on a rolling time window

PaigeMiller — Wed, 17 Apr 2019 19:16:54 GMT

As I re-read my original suggestion, its not clear why you have 2 identical ID variables right after each other, and this was not something I envisioned. Explain further how this happens, and please show us a small example.

Re: Trying to create a new binary variable based on a rolling time window

novinosrin — Wed, 17 Apr 2019 19:29:00 GMT

Hi @righcoastmike Thank your for your patience and feedback. Can you post a more comprehensive sample of your HAVE and the WANT. I have a feeling, I am failing to understand the requirement somewhere.

Objective : To find study id's have 3 or more ed_visits within 12 months ( we'll call them repeat users)

My understanding: If interval between "any 2 dates is <=12", that qualifies. So if month interval between first record of studyid and 3rd record or nth record of studyid is <=12, that still qualifies.

So once we have the dates extracted

md=input(compress(mth_date,'-'),yymmn6.);

Here is how the HASH logic works:

1. Dataset temp with extracted date is sorted by studyid for By group processing

2. For every observation of By group, the look up happens for all obs for it's equivalent. So if studyid has 3 obs as in the sample, the look up happens 3*3=9 times i.e 1 obs of studyid1 with all 3 obs of studyid 1, then 2nd obs of studyid1 with all 3 obs of studyid 1 etc.

3. Since it is a look ahead(next set of records i.e future dates) the look up(hash table) md variable should be greater than the current md variable of the table from which the look up is taking place.

4. Should 3 be true, we check whether number of months between the two dates is <=12. If 3 and 4 happen to be true, we sum ed_visit of current +ed_visits of the future(look up table)

4. The sum is a running total across each by group

5. At the end of each by group should the cumulative sum is >=3 then that entire group should be flagged as 1 else 0

6. In instances, where there is only one record for a by group, should the value of ed_visits is >=3 that should also be flagged as 1.

If the above is correct, I think where i perhaps was an idiot is likely this part that I am noticing now, that misses it is

The logic requires change to-->

do rc=h.find() by 0 while(rc=0);
if md>_md and intck('month',_md,md)<=12 then do;
k=sum(ed_visits,_ed_visits);
_k+k;
end;
rc=h.find_next();
end;

from the earlier below

do rc=h.find() by 0 while(rc=0);
if md>_md and intck('month',_md,md)<=12 then k=sum(ed_visits,_ed_visits);
rc=h.find_next();
end;
_k+k;

So the changed full version is


data want;
if _n_=1 then do;
	if 0 then set have;
   dcl hash H (dataset:'have',ordered: "A",multidata:'y') ;
   h.definekey  ("studyID") ;
   h.definedata ("md","ed_visits") ;
   h.definedone () ;
end;
_k=0;
do until(last.studyid);
set have(rename=(md=_md ed_visits=_ed_visits));
by studyID;
do rc=h.find() by 0 while(rc=0);
if md>_md and intck('month',_md,md)<=12 then do;
k=sum(ed_visits,_ed_visits);
_k+k;
end;
rc=h.find_next();
end;
end;
repeat_user=first.studyid and last.studyid and _ed_visits>=3  or k>=3 ;
do until(last.studyid);
set have;
by studyID;
output;
end;
keep studyid mth_date ed_visits year repeat_user;
run;

Let me know. Sorry and Thank you for your patience.

Re: Trying to create a new binary variable based on a rolling time window

righcoastmike — Wed, 17 Apr 2019 23:24:28 GMT

Hi @novinosrin and @PaigeMiller

First off, I really appreciate the time you are spending on this. It's really helping me learn a lot.

Secondly, @novinosrin you have no need to apologize, you are both doing me a huge service helping me out on this, and anyone who would be impatient when someone is volunteering to help them probably doesn't deserve your help. Thank you.

Since things have changed a bit after a day of working on this, I thought a general update and summary of where we are at would be helpful.

1. I've managed to convert the date variables in the dataset to SAS dates, so my dataset now looks something like this:

data have;
input studyID date :anydtdte10. ed_visits year ;
cards;
1 2011-04 0 2011
1 2011-05 1 2011
1 2012-01 3 2012
1 2012-04 0 2012
1 2013-05 2 2013
1 2013-06 0 2013
2 2011-03 4 2011
3 2011-04 2 2011
3 2011-06 0 2011
3 2013-08 2 2013
4 2011-04 1 2011
4 2012-05 1 2012
4 2013-06 1 2013
4 2014-07 1 2014
5 2011-03 0 2011
5 2012-05 0 2012
5 2013-08 4 2013
5 2014-02 0 2013
run;

Ideally, I would like to end up with a dataset that looks like this

data want;
input studyID date :anydtdte10. ed_visits year repeat_user ;
cards;
1 2011-04 0 2011 1
1 2011-05 1 2011 1
1 2012-01 2 2012 1
1 2012-04 0 2012 1 
1 2013-05 2 2013 1
1 2013-06 0 2013 1
2 2011-03 4 2011 1
3 2011-04 2 2011 0
3 2011-06 0 2011 0
3 2013-08 2 2013 0
4 2011-04 1 2011 0
4 2012-05 1 2012 0
4 2013-06 1 2013 0
4 2014-07 1 2014 0
5 2011-03 0 2011 1
5 2012-05 0 2012 1
5 2013-08 4 2013 1
5 2014-02 0 2013 1
run;

where 1 indicates a repeat user.

Our criteria for a repeat user is 3 or more ed visits within a 12 month period.

So in this case:

#1 is a repeat user because they had 3 visits between 2011-05 and 2012-01 (less than 12 months apart). The 2 visits in 2013-05 don't affect anything

#2 is a repeat user, because even though we only have one month of data for them, they had 4 visits in the month

#3 is not a repeat user because even though they had 4 visits, 2 were in 2011 and 2 were in 2013 (more than 12 months apart)

#4 is not a repeat user because they only had 1 visit in each year, with more than 12 months between them.

#5 is a repeat user for the same reason as #2, we just have more data because they were in the study longer.

Thus far we've tried:

Proc expand - looked promising but ran into trouble because of overlapping dates. for example, both #1 and #2 and #4 have records for 2011-04. proc throws up an error because of the duplicate values in the ID field (date)

@novinosrin 's hash method - This one feel like we're close, but while it does a good job of identifying cases like #1, it misses cases like #2 and #5 - I've tried the updated code and get the same results as before.

@novinosrin 's sql method - This one is also close, but has the opposite problem of the Hash method, it's capturing many records that have less than three ed visits within 12 months, I haven't spotted any particular pattern yet, but I'll let you know if I find anything.

and that's where we are at. I'm going to keep playing with the code I have to see if I can't figure out where things are going wrong, but I hope this clarifies things. Any further insights would be very much appreciated.

Thank you both so much

Mike

Re: Trying to create a new binary variable based on a rolling time window

novinosrin — Thu, 18 Apr 2019 00:37:48 GMT

Hi and Good evening @righcoastmike A silly change that i missed to make

The k>3 below

repeat_user=first.studyid and last.studyid and _ed_visits>=3 or k>=3 ;

should have been _k>3 with a prefix _ underscore coz that's the accumlating var

So I tested with the changed below

repeat_user=first.studyid and last.studyid and _ed_visits>=3 or _k>=3 ;

Here -->

/*Again I converted to SAS dates, this is no big deal*/
data have;
input studyID date $ ed_visits year ;
md=input(compress(date,'-'),yymmn6.); /*notice here the converted SAS date variable*/
format md date9.;
cards;
1 2011-04 0 2011
1 2011-05 1 2011
1 2012-01 3 2012
1 2012-04 0 2012
1 2013-05 2 2013
1 2013-06 0 2013
2 2011-03 4 2011
3 2011-04 2 2011
3 2011-06 0 2011
3 2013-08 2 2013
4 2011-04 1 2011
4 2012-05 1 2012
4 2013-06 1 2013
4 2014-07 1 2014
5 2011-03 0 2011
5 2012-05 0 2012
5 2013-08 4 2013
5 2014-02 0 2013
run;


/*the same code with _k>3 lol hahaha*/
data want;
if _n_=1 then do;
	if 0 then set have;
   dcl hash H (dataset:'have',ordered: "A",multidata:'y') ;
   h.definekey  ("studyID") ;
   h.definedata ("md","ed_visits") ;
   h.definedone () ;
end;
_k=0;
do until(last.studyid);
set have(rename=(md=_md ed_visits=_ed_visits));
by studyID;
do rc=h.find() by 0 while(rc=0);
if md>_md and intck('month',_md,md)<=12 then do;
k=sum(ed_visits,_ed_visits);
_k+k;
end;
rc=h.find_next();
end;
end;
repeat_user=first.studyid and last.studyid and _ed_visits>=3  or _k>=3 ;
do until(last.studyid);
set have;
by studyID;
output;
end;
keep studyid date md ed_visits year repeat_user;
run;

Proc print noobs;run;

Here are the test results;

study repeat_
ID date ed_visits year md user

1 2011-04 0 2011 01APR2011 1
1 2011-05 1 2011 01MAY2011 1
1 2012-01 3 2012 01JAN2012 1
1 2012-04 0 2012 01APR2012 1
1 2013-05 2 2013 01MAY2013 1
1 2013-06 0 2013 01JUN2013 1
2 2011-03 4 2011 01MAR2011 1
3 2011-04 2 2011 01APR2011 0
3 2011-06 0 2011 01JUN2011 0
3 2013-08 2 2013 01AUG2013 0
4 2011-04 1 2011 01APR2011 0
4 2012-05 1 2012 01MAY2012 0
4 2013-06 1 2013 01JUN2013 0
4 2014-07 1 2014 01JUL2014 0
5 2011-03 0 2011 01MAR2011 1
5 2012-05 0 2012 01MAY2012 1
5 2013-08 4 2013 01AUG2013 1
5 2014-02 0 2013 01FEB2014 1

Please let me know at your earliest convenience.

Re: Trying to create a new binary variable based on a rolling time window

righcoastmike — Thu, 18 Apr 2019 01:39:18 GMT

Hi @novinosrin

Ok we're getting somewhere now, the latest code made it so the hash and the sql are producing the same results, they aren't quite correct but at least it could help to identify the problem. Here is a complete example of a study id that has been misidentified as a 1 when it should be a zero:

data have;
input studyID date $ ed_visits year ;
md=input(compress(date,'-'),yymmn6.); /*notice here the converted SAS date variable*/
format md date9.;
cards; 
1 2011-04 0 2011
1 2011-05 0 2011
1 2011-06 0 2011
1 2011-07 0 2011
1 2011-08 0 2011
1 2011-09 0 2011
1 2011-10 0 2011
1 2011-11 0 2011
1 2011-12 0 2011
1 2012-01 0 2012
1 2012-02 0 2012
1 2012-03 2 2012
1 2012-04 0 2012
1 2012-05 0 2012
1 2012-06 0 2012
1 2012-07 0 2012
1 2012-08 0 2012
1 2012-09 0 2012
1 2012-10 0 2012
1 2012-11 0 2012
1 2012-12 0 2012
1 2013-01 0 2013
1 2013-02 0 2013
1 2013-03 0 2013
1 2013-04 0 2013
1 2013-05 0 2013
1 2013-06 0 2013 
run;

I ran the code on just this studyid alone, and the misclassification persists. The only thing I can think of is that the fact that there are 27 records associated with the one studyid is throwing it off.

Thoughts?

Mike

Re: Trying to create a new binary variable based on a rolling time window

righcoastmike — Thu, 18 Apr 2019 01:41:22 GMT

sorry for the weird code, I'm not sure what happened there.

here's a better version.

mike

data have;
input studyID date $ ed_visits year ;
md=input(compress(date,'-'),yymmn6.); /*notice here the converted SAS date variable*/
format md date9.;
cards; 
1 2011-04 0 2011
1 2011-05 0 2011
1 2011-06 0 2011
1 2011-07 0 2011
1 2011-08 0 2011
1 2011-09 0 2011
1 2011-10 0 2011
1 2011-11 0 2011
1 2011-12 0 2011
1 2012-01 0 2012
1 2012-02 0 2012
1 2012-03 2 2012
1 2012-04 0 2012
1 2012-05 0 2012
1 2012-06 0 2012
1 2012-07 0 2012
1 2012-08 0 2012
1 2012-09 0 2012
1 2012-10 0 2012
1 2012-11 0 2012
1 2012-12 0 2012
1 2013-01 0 2013
1 2013-02 0 2013
1 2013-03 0 2013
1 2013-04 0 2013
1 2013-05 0 2013
1 2013-06 0 2013 
run;