topic Re: Flag only once if the criteria is met in SAS Programming

Flag only once if the criteria is met

NickS2 — Thu, 28 May 2020 20:42:47 GMT

I want to flag the treatment if certain conditions are met but I don't want to flag it again for the same patient. The condition here is, if the patient is taking drug A. B, C or D for the very first time and the ENROL_FLAG = "Y". If the first condition doesn't get satisfied then the second condition is that ENROL_FLAG = "Y" and the DAY_DIFF > 180.

Also if the patient is receiving two or more treatment on the same day where he is meeting this criteria all the observations should get flagged.

The code I wrote is,

data WANT;

set HAVE;

by ID DATE;

if first.ID and ENROL_FLAG = "Y" then TRT_FLAG= "Y";

else if ENROL_FLAG="Y" and DAY_DIFF > 180 then TRT_FLAG="Y";

run;

This is how my data looks like:

ID DRUG DATE VISIT ENROL_FLAG DAY_DIFF

1 A 29APR2018 1 Y .

1 D 29APR2018 1 Y .

1 A 21MAY2019 2 Y 22

1 D 21MAY2019 2 Y 22

2 B 27JUN2015 1 Y .

2 B 26JUN2016 2 Y 365

3 C 03MAR2015 1 N .

3 B 03MAR2015 1 N .

3 C 12SEP2015 2 Y 193

3 B 12SEP2015 2 Y 193

The result I am getting is:

ID DRUG DATE VISIT ENROL_FLAG DAY_DIFF TRT_FLAG

1 A 29APR2018 1 Y . Y

1 D 29APR2018 1 Y .

1 A 21MAY2019 2 Y 22

1 D 21MAY2019 2 Y 22

2 B 27JUN2015 1 Y . Y

2 B 26JUN2016 2 Y 365 Y

3 C 03MAR2015 1 N .

3 B 03MAR2015 1 N .

3 C 12SEP2015 2 Y 193 Y

3 B 12SEP2015 2 Y 193

The result I want:

ID DRUG DATE VISIT ENROL_FLAG DAY_DIFF TRT_FLAG

1 A 29APR2018 1 Y . Y

1 D 29APR2018 1 Y . Y

1 A 21MAY2019 2 Y 22

1 D 21MAY2019 2 Y 22

2 B 27JUN2015 1 Y . Y

2 B 26JUN2016 2 Y 365

3 C 03MAR2015 1 N .

3 B 03MAR2015 1 N .

3 C 12SEP2015 2 Y 193 Y

3 B 12SEP2015 2 Y 193 Y

Re: Flag only once if the criteria is met

DavePrinsloo — Thu, 28 May 2020 21:15:12 GMT

You need to use the retain statement! Use it so that TRT_FLAG keeps it value as you go through the data.

Re: Flag only once if the criteria is met

novinosrin — Thu, 28 May 2020 21:11:02 GMT

Hi @NickS2 It seems to me the datediff> condition doesn't quite make sense,


data have;
input ID      DRUG $          DATE :date9.          VISIT           ENROL_FLAG  $      DAY_DIFF	;
format date date9.;
cards;
1          A          29APR2018            1                        Y                           .

1          D          29APR2018            1                        Y                           .

1          A          21MAY2019            2                        Y                          22

1          D          21MAY2019            2                        Y                          22

2          B          27JUN2015             1                        Y                           .

2          B          26JUN2016             2                        Y                        365 

3          C          03MAR2015            1                        N                          .

3          B          03MAR2015            1                        N                          .

3          C          12SEP2015             2                        Y                        193

3          B          12SEP2015             2                        Y                        193
;


data WANT;
 if _n_=1 then do;
   dcl hash H () ;
   h.definekey  ("drug") ;
   h.definedone () ;
 end;
 do _n_=h.clear() by 0 until(last.id);
  set HAVE;
  by ID ;
  TRT_FLAG=' ';
  if  ENROL_FLAG = "Y" and  h.check()ne 0 then do;
   TRT_FLAG='Y';
   h.add();
  end;
  output;
 end;
run;

Re: Flag only once if the criteria is met

NickS2 — Thu, 28 May 2020 21:15:56 GMT

Thank you for the reply Dave but if I will use the retain statement as you suggested it is flagging all the observations for that ID which I don't want. I just want to flag only those observations which are meeting the criteria I suggested above.

Re: Flag only once if the criteria is met

Jagadishkatam — Thu, 28 May 2020 21:20:01 GMT

please try the below code

proc sort data=have;
by id drug date;
run;

data want;
set have;
by id drug date;
if first.drug and ENROL_FLAG='Y' then TRT_FLAG=ENROL_FLAG;
if not first.drug and ENROL_FLAG='Y' and  DAY_DIFF>180 then TRT_FLAG=ENROL_FLAG;
run;

Re: Flag only once if the criteria is met

NickS2 — Thu, 28 May 2020 21:31:50 GMT

Thank you Novino,

I ran your code but it is flagging the ID number 2's both visits which I don't want.

Re: Flag only once if the criteria is met

NickS2 — Thu, 28 May 2020 21:33:28 GMT

Thanks Jagdish,

But youe code is giving me the same result which I am getting.

Re: Flag only once if the criteria is met

novinosrin — Thu, 28 May 2020 23:08:13 GMT

"Thank you Novino,

I ran your code but it is flagging the ID number 2's both visits which I don't want."

Hi @NickS2 Are you sure that's the case. Did you really look at the results carefully? Here is my test and results-

data have;
input ID      DRUG $          DATE :date9.          VISIT           ENROL_FLAG  $      DAY_DIFF	;
format date date9.;
cards;
1          A          29APR2018            1                        Y                           .

1          D          29APR2018            1                        Y                           .

1          A          21MAY2019            2                        Y                          22

1          D          21MAY2019            2                        Y                          22

2          B          27JUN2015             1                        Y                           .

2          B          26JUN2016             2                        Y                        365 

3          C          03MAR2015            1                        N                          .

3          B          03MAR2015            1                        N                          .

3          C          12SEP2015             2                        Y                        193

3          B          12SEP2015             2                        Y                        193
3          B          13MAR2016             2                        Y                        193
;


data WANT;
 if _n_=1 then do;
   dcl hash H () ;
   h.definekey  ("drug") ;
   h.definedone () ;
 end;
 do _n_=h.clear() by 0 until(last.id);
  set HAVE;
  by ID ;
  TRT_FLAG=' ';
  if  ENROL_FLAG = "Y" and  h.check()ne 0 then do;
   TRT_FLAG='Y';
   h.add();
  end;
  output;
 end;
run;

proc print noobs;run;

RESULTS:

Re: Flag only once if the criteria is met

NickS2 — Fri, 29 May 2020 14:04:45 GMT

@novinosrin the results are not as expected when I applied this code to my dataset as there are patients who have taken different drug after a gap of more than 180 days and that is why it is flagging them again.

For example if the patient ID 2 is taking C drug in his second visit then this code will raise the TRT_FLAG again.

Re: Flag only once if the criteria is met

novinosrin — Fri, 29 May 2020 14:13:11 GMT

Can you post a more comprehensive sample plz?

Re: Flag only once if the criteria is met

NickS2 — Fri, 29 May 2020 14:38:41 GMT

@novinosrin

ID DRUG DATE VISIT ENROL_FLAG DAY_DIFF

1 A 29APR2018 1 Y .

1 D 29APR2018 1 Y .

1 A 21MAY2019 2 Y 22

1 D 21MAY2019 2 Y 22

2 B 27JUN2015 1 Y .

2 B 26JUL2015 2 Y 29

2 C 26JUN2016 3 Y 365

2 C 27JUL2016 4 Y 31

3 C 03MAR2015 1 N .

3 B 03MAR2015 1 N .

3 C 12SEP2015 2 Y 193

3 B 12SEP2015 2 Y 193

3 D 15OCT2016 3 Y 388

3 D 14NOV2016 4 N 29

4 B 01NOV2014 1 N .

4 B 28NOV2014 2 N 27

4 B 23DEC2014 3 N 25

4 B 01AUG2015 4 N 247

5 D 12MAR2015 1 Y .

5 D 09APR2015 2 Y 28

5 D 03MAY2015 3 Y 22

5 C 10JUN2016 4 Y 401

5 C 03JUL2016 5 Y 24

I expect one patient to be flagged only once for a particular visit if he/she meets the criteria. The criteria is either it has to be patients first visit and ENROL_FLAG = "Y" and if this criteria is not satisfied then irrespective of the visit the ENROL_FLAG = "Y" and DAY_DIFF>180 days.

Also if the patient has multiple record when he/she is meeting the criteria then all the records should be flagged. Like for patient ID 1, as he is meeting the criteria in his first visit, both the records should be flagged for treatment.

Thank you

Re: Flag only once if the criteria is met

novinosrin — Fri, 29 May 2020 15:45:12 GMT

Hi @NickS2 Thank you. Right, Sounds much simpler than I thought, Can you please try the below and let me know. The logic is as follows:

1. If visit=1 and ENROL_FLAG = "Y" , then flag=y

2. Should the above (1) is satisfied no more processing needed for a particular ID

3. If 1 is not satisfied, look for visits>1 and check for ENROL_FLAG = "Y" and datediff>180 and flag all those that satisfy this conditions.

If the above 3 is correct, the following should do-


data want;
 do until(last.id);
  set have;
  by id ;
  trt_flag=' ';
  if visit=1 and ENROL_FLAG = "Y"  then do;
   trt_flag="Y";
   _n_=0;
  end;
  else if _n_ and ENROL_FLAG = "Y" and DAY_DIFF>180 then trt_flag="Y";
  output;
 end; 
run;

Re: Flag only once if the criteria is met

NickS2 — Fri, 29 May 2020 17:23:00 GMT

Thank you @novinosrin, it worked flawlessly.

I have one more question. Can I delete the all the records for the patients who did not meet the treatment criteria and previous date records of the patients who meet the treatment criteria?

To be more precise I don't want any records of the patients who did not meet the treatment criteria but want to keep all the records of the patient who meet the treatment criteria except those records which are pre-dated to the treatment criteria met date.

Re: Flag only once if the criteria is met

mklangley — Fri, 29 May 2020 17:28:54 GMT

@NickS2

Shouldn't the visit for patient 2 on 26JUN2016 (drug B) be flagged as TRT_FLAG = Y? Your desired output lists it as blank. The first criteria is not satisfied (since the patient already has had drug B), but the second is (ENROL_FLAG = Y and DAY_DIFF > 180).

Re: Flag only once if the criteria is met

PGStats — Fri, 29 May 2020 17:34:56 GMT

I think the proper logic would look like this:


proc sort data=have; by id drug date; run;

data want;
retain flagged;
set have; by id drug;
if first.drug then flagged = 0;
if not flagged then if ENROL_FLAG="Y" or DAY_DIFF > 180 then do;
    flagged = 1;
    TRT_FLAG = "Y";
    end;
drop flagged;
run;

proc sort data=want; by id date drug; run;

Re: Flag only once if the criteria is met

novinosrin — Fri, 29 May 2020 17:36:15 GMT

@NickS2 Are you asking just to keep the records if trt_flag="Y" ?

Or "but want to keep all the records of the patient who meet the treatment criteria except those records which are pre-dated to the treatment criteria met date." -Am i missing something here?

For example, if trt_flag="Y" qualifies for 7 records in the given sample output. So a subset would mean tweaking the current

Output;

to if trt_flag='Y' then output;

Re: Flag only once if the criteria is met

NickS2 — Fri, 29 May 2020 18:05:33 GMT

@novinosrin

I want to keep all the records of the patient who meet the treatment criteria except those records which are pre-dated to the treatment criteria met date.

Thank you

Re: Flag only once if the criteria is met

NickS2 — Fri, 29 May 2020 18:08:26 GMT

@mklangley No the second record for the patient ID 2 should be not get flagged because the first record already met the criteria.

Re: Flag only once if the criteria is met

novinosrin — Fri, 29 May 2020 18:11:38 GMT

so is it just

  if trt_flag='Y' then output;

in place of

  output;

Re: Flag only once if the criteria is met

NickS2 — Fri, 29 May 2020 18:21:07 GMT

@novinosrin

This just gives me the records which are flagged and not the after date records of the patient who got flagged. Like for patient ID 1 the first record gets flagged so I want all the 4 records for that patient but for patient ID 3 the third record gets flagged then I want all the records after third record (including third record).

I hope you got my point.