topic Re: Flag for different codes existing in a 30 day period in SAS Programming

Flag for different codes existing in a 30 day period

jmmedina25 — Mon, 01 Aug 2022 19:14:26 GMT

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

Re: Flag for different codes existing in a 30 day period

PaigeMiller — Mon, 01 Aug 2022 19:50:38 GMT

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.

Re: Flag for different codes existing in a 30 day period

mkeintz — Mon, 01 Aug 2022 22:51:05 GMT

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.

Re: Flag for different codes existing in a 30 day period

Ksharp — Tue, 02 Aug 2022 12:00:53 GMT

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;

Re: Flag for different codes existing in a 30 day period

mkeintz — Tue, 02 Aug 2022 15:04:09 GMT

@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.

Re: Flag for different codes existing in a 30 day period

Ksharp — Wed, 03 Aug 2022 12:52:01 GMT

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