Flag for different codes existing in a 30 day period

[jmmedina25]

Hello,  I'm looking to create a flag for members who have 2 or more different codes within the span of 30 days and the same location.  There are 4 possible codes a member (24, 25, 26, 27) can have and they can have the same code more than once.  Here's and example of the data and the flag I would like to create:

Memberid    date              locationid   code      

1                  03Feb2022    12               24        

1                  24Feb2022    12               24

1                  28Mar2022    13               24

2                  03Feb2022    13               24        

2                  24Feb2022    13               25

3                  03Feb2022    12               24        

3                  24Feb2022    13               26

output data:

Memberid   Flag      

1                 0       

2                 1

3                 0         

[PaigeMiller]

proc sql;
    create table want as select memberid,count(distinct code)>1 as flag
    from have 
    group by memberid;
quit;

This should match your output. However, your output does not match your description of the problem, the 30 day period is ignored. Perhaps you can explain how that is used, and what the output should look like if the 30 day time period is used.

[mkeintz · Accepted solution]

If the data are sorted by memberid/locationid/date, then this should do:

data want (keep=memberid flag);
  set have;
  by memberid locationid date ;
  if first.memberid=1 then flag=0;
  retain flag;
  if first.locationid=0 and code^=lag(code) and dif(date)<=30 then flag=1;
  if last.memberid;
run;
  

Flag is set to zero at first record for a memberid.  All subsequent records are checked to see if flag should be 1.  It's never reset from 1 to zero for a memberid.

The flag check tests whether (1) the record in hand is NOT the start of a locationid, (2) the current code differs from the prior code, and (3) the current date minus last date <=30.  

[Ksharp]

data have;
input Memberid    date    : date9.          locationid   code      ;
format date date9.;
cards;
1                  03Feb2022    12               24        
1                  24Feb2022    12               24
1                  28Mar2022    13               24
2                  03Feb2022    13               24        
2                  24Feb2022    13               25
3                  03Feb2022    12               24        
3                  24Feb2022    13               26
;

proc sql;
create table want as
 select memberid,max(flag) as flag
  from (
select memberid,locationid,count(distinct code)>1 and range(date)<=30 as flag
    from have 
    group by memberid,locationid
)
group by memberid
;
quit;

[mkeintz]

---

@Ksharp wrote:

data have;
input Memberid    date    : date9.          locationid   code      ;
format date date9.;
cards;
1                  03Feb2022    12               24        
1                  24Feb2022    12               24
1                  28Mar2022    13               24
2                  03Feb2022    13               24        
2                  24Feb2022    13               25
3                  03Feb2022    12               24        
3                  24Feb2022    13               26
;

proc sql;
create table want as
 select memberid,max(flag) as flag
  from (
select memberid,locationid,count(distinct code)>1 and range(date)<=30 as flag
    from have 
    group by memberid,locationid
)
group by memberid
;
quit;

---

This is different than how I read the OP's request

      "2 or more different codes within the span of 30 days and the same location"

I took it to mean that a given location may have a total date range >30 but would still get FLAG=1 if any two distinct codes were within 30 days of each other.

@jmmedina25:   If instead you mean that total range of all dates is <=30 then use @Ksharp's suggestion and discard my reply.

[Ksharp]

Yeah. Maybe I understand wrong . It is all depend what OP want. OP can test it and pick up the correct one .