How do I flag 5 values that occur within 30 day time span for each observation.

[sharise]

I have dataset attached below. This dataset is simulating hospital/clinic data. Each observation defines a visit. A visit is defined as the unique combination of person, date, provider.

I am trying to flag persons with at least 5 visits within a 30 day time span. I know how to use where and if statements and I have read from other forums about counting consecutive variable values but I'm unsure how to program for a range of within 30 days. Please excuse me if this is a very simple question but I am not that advanced of a SAS user and having a hard time visualizing this.

I am trying a proc statement but I don't know what functions can give me the 5 visits within 3 days. I am stuck on code below. I don't know if I should even be trying to do this using a proc freq. Maybe I should be doing a data step?

proc freq data=p.persons;
where date =
run; 

My version of SAS is 9.04.01M6P110718

[sbxkoenk]

Hello @sharise ,

The below is far from the most elegant solution possible, but, hey, it's weekend. 😉

data have;
 LENGTH personID $ 3 date 8 providerID $ 3;
 format date date9.;
 input personID $ date : date9. providerID $;
 datalines;
PE1  12FEB2012  PR7
PE1  28FEB2012  PR7
PE1  07MAR2012  PR8
PE1  12MAR2012  PR2
PE1  22MAR2012  PR7
PE1  24MAR2012  PR7
PE1  01APR2012  PR7
PE9  12FEB2014  PR2
PE9  24FEB2014  PR3
PE9  01APR2014  PR2
PE9  07APR2014  PR3
PE9  17APR2014  PR2
PE9  19APR2014  PR2
PE9  30APR2014  PR3
PE9  08MAY2014  PR3
PE8  14FEB2014  PR2
PE8  15FEB2014  PR3
PE8  16FEB2014  PR2
PE8  17FEB2014  PR3
PE8  18FEB2014  PR2
PE8  19FEB2014  PR2
PE8  20FEB2014  PR3
PE8  21FEB2014  PR3
;
run;

proc sort data=have;
 by personID date providerID;
run;

data have1(drop = date providerID);
 LENGTH personID $ 3 ALLvisits $ 1100;
 set have;
 by personID date providerID;
 retain ALLvisits '';
 if first.personID     then ALLvisits=put(date,date9.);
 if NOT first.personID then ALLvisits = trim(left(ALLvisits)) !! '#' !! put(date,date9.);
 if last.personID then output;
run;

data have2;
 merge have have1;
 by personID;
run;

data want(drop = di);
 set have2(rename=(date=date_from));
 count=0;
 do di = (date_from) to (date_from + 30);
  if index(ALLvisits,put(di,date9.))>0 then count=count+1; 
 end;
 format di date9.;
run;

PROC MEANS data=want MAX NWAY noprint;
 CLASS personID;
 var count;
 output out=work.want_out MAX= / autoname;
run;

PROC DATASETS LIBRARY=WORK NoList;
 modify want_out;
  label count_Max = 'Maximum number of visits in a 30 days timeframe'; run;
QUIT;
/* end of program */

Good luck with learning SAS,

Koen

[sbxkoenk]

See also previous reply !!

Other approaches I am thinking of :

• using a hash table for lookup
• overlapping time intervals as in the below blog

Calculating the overlap of date/time intervals
By Leonid Batkhan on SAS Users January 13, 2022
https://blogs.sas.com/content/sgf/2022/01/13/calculating-the-overlap-of-date-time-intervals/

Koen

[mkeintz]

I take the objective

I am trying to flag persons with at least 5 visits within a 30 day time span

to mean you want a dataset with one record per person, with a flag variable indicating that they had at least one 30 day span with 5 or more visits.

And the input data is a one-record-per-visit file, sorted by person id (variable PERSON) and date (DATE)..  So you can't go directly to a PROC with a where filter.  You need to create a new one-obs-per-person dataset from the one-obs-per-visit dataset.

Here is a simple example.

data persons (keep=person flag);
  set visits ; 
  by person date;
  retain flag;

  if first.id then flag=0;
  if lag4(id)=id and lag4(date)>=date-30 then flag=1;
  if last.id;
run;
proc freq data=persons;
  tables flag;
run;

As I mentioned above this expects the VISITS dataset to be sorted by PERSON/DATE.  The BY statement above really only requires BY PERSON to produce the desired results - it doesn't need the DATE variable in the BY statement.  But I put it there because SAS will stop the data step and let you know if any DATE is out of order.

Now if you need something more than this simplistic PERSONS dataset, let us know.

[sbxkoenk]

omg, it can be as simple as that of course.

Again, it's the effect of the weekend, shall we say. 😏😴

Koen

[tarheel13]

you could use inexact matching with Proc SQL.