topic Re: Creating flag and additional record in SAS Programming

Creating flag and additional record

ambadi007 — Mon, 11 Apr 2022 09:44:00 GMT

Hi team,

i have a tricky requirement with 3 scenarios , scenario 1 i need to create an additional flag=y with the closest value of awtarget

Scenario 1 Same subject same param same avisitn with different ADY (AWDIFF=awtarget-ady)

USUBJID VISIT VISITNUM AVISIT AVISITN PARAM AVAL AWTARGET ADY AWDIFF
101 WEEK1 25 Week 1 20 PAR1 55 5 4 1
101 WEEK 1 25 Week 1 20 PAR1 50 5 3 2

(closest values of AWDIFF with awtarget will be flagged FLG="Y" so here the first record should be flagged as Y
since the closest awdiff is low for that record )

Scenario 2 Same subject same param same avisitn with same ADY (AWDIFF=awtarget-ady)

USUBJID VISIT VISITNUM AVISIT AVISITN PARAM AVAL AWTARGET ADY AWDIFF

102 WEEK 1 25 Week 1 20 PAR1 55 5 4 1
102 WEEK 1 25 Week 1 20 PAR1 50 5 4 1

(need to create an additional record with average of AVAL for these multiple records and create new variable dtype="AVERAGE" and FLG="Y" for that record)

Scenario 3 Same subject same param different avisitn with same ADY and AWDIFF=0

USUBJID VISIT VISITNUM AVISIT AVISITN AVAL PARAM AWTARGET ADY AWDIFF
103 UNS 20.01 Week 1 20.01 20 PAR1 55 5 5 0
103 UNS 20.02 Week 1 20.02 20 PAR1 50 5 5 0
103 UNS 20.03 Week 1 20.03 20 PAR1 50 5 5 0

need to create an additional record with average of AVAL for these multiple records and create new variable dtype="AVERAGE" and FLG="Y"

so the needed output is below

USUBJID VISIT VISITNUM AVISIT AVISITN PARAM AVAL AWTARGET ADY AWDIFF FLG DTYPE
101 WEEK 1 25 Week 1 20 PAR1 55 5 4 1 Y
101 WEEK 1 25 Week 1 20 PAR1 50 5 3 2
102 WEEK 1 25 Week 1 20 PAR1 5 5 5 4 1
102 WEEK 1 25 Week 1 20 PAR1 50 5 4 1
102 WEEK 1 25 Week 1 20 PAR1 Avg 5 4 1 Y AVG
etc...

Re: Creating flag and additional record

Oligolas — Mon, 11 Apr 2022 11:02:55 GMT

what should the avisitn be for scenario 3?

Re: Creating flag and additional record

ambadi007 — Mon, 11 Apr 2022 11:10:54 GMT

We can give as 999

Re: Creating flag and additional record

PaigeMiller — Mon, 11 Apr 2022 11:50:22 GMT

@ambadi007 wrote:

i have a tricky requirement with 3 scenarios , scenario 1 i need to create an additional flag=y with the closest value of awtarget

Closest to what?

Re: Creating flag and additional record

ambadi007 — Mon, 11 Apr 2022 11:56:12 GMT

The record which is having low awdiff will be close value

Re: Creating flag and additional record

PaigeMiller — Mon, 11 Apr 2022 12:03:07 GMT

@ambadi007 wrote:
The record which is having low awdiff will be close value

Odd terminology. Why don't you just say the lowest value?

So if on one row AWDIF is -7 and another row AWDIF is +1, then we want the -7

Re: Creating flag and additional record

ambadi007 — Mon, 11 Apr 2022 12:05:01 GMT

yes for the first scenario we have to take the lowest value

Re: Creating flag and additional record

ambadi007 — Mon, 11 Apr 2022 13:22:00 GMT

could you please help me on this .. its little bit urgent now

Re: Creating flag and additional record

Oligolas — Mon, 11 Apr 2022 13:42:10 GMT

you could do something like this:

data have;
input USUBJID $ VISIT $ VISITNUM AVISIT $ AVISITN PARAM $ AVAL AWTARGET ADY AWDIFF;
datalines;
101 WEEK1 25 Week1 20 PAR1 55 5 4 1
101 WEEK1 25 Week1 20 PAR1 50 5 3 2
102 WEEK1 25 Week1 20 PAR1 55 5 4 1
102 WEEK1 25 Week1 20 PAR1 50 5 4 1
103 UNS 20.01 Week1 20.01 PAR1 55 5 5 0
103 UNS 20.02 Week1 20.02 PAR1 50 5 5 0
103 UNS 20.03 Week1 20.03 PAR1 50 5 5 0
;
run;

proc sort data=have; by usubjid param avisit descending AWDIFF; run;

data want;
set have;
length DTYPE $40;
call missing(dtype);
by usubjid param avisit descending AWDIFF;
*Define help vars;
retain n . countflg . avgn . avgsum . avgcalc . avisitnchg .;
*SC1;
if first.AWDIFF then countflg=0;
countflg+1;
if last.avisit and countflg=1 then flg='Y';

*SC2/3;
lag1_avisitn=lag1(avisitn);
if first.avisit then do;
   avgn=0;
   avgsum=0;
   avgcalc=0;
   avisitnchg=0;
   lag1_avisitn=.;
end;

avgn+1;
if avisitn ne lag1_avisitn then avisitnchg+1;
if not missing(aval) then avgsum=sum(avgsum,aval);
avgcalc=avgsum/avgn;

if last.awdiff and avgn>1 and countflg=avgn then do;
   output;   
   *SC2/3;
   avg=avgcalc;
   dtype='AVG';
   *SC3;
   if avisitnchg=avgn then avisitn=999;
   output;
end;
else output;
drop n countflg lag1_avisitn avgn avgsum avgcalc avisitnchg;
run;

Re: Creating flag and additional record

ambadi007 — Mon, 11 Apr 2022 16:02:33 GMT

Thank you for providing the solution for this tricky requirement. i have below query for the code . when the below criteria is coming it is not giving exact output . the below the output is provided

USUBJID VISIT VISITNUM AVISIT AVISITN PARAM AVAL AWTARGET ADY AWDIFF DTYPE FLG
101 Week20 20.01 Week 1 20 PAR1 55 141 169 28
101 UNS20.02 20.02 Week 1 20 PAR1 50 141 169 28

101 UNS20.02 20.02 Week 1 20 PAR1 50 141 169 28 AVG
101 Week44 20.03 Week 1 20 PAR1 50 5 5 0 Y

In this scenario the Flag and Dtype are differently coming in output. the flag should come for the newly created record only (the highlighted record) . not for a different record.

Re: Creating flag and additional record

Oligolas — Tue, 12 Apr 2022 06:12:28 GMT

I've adapted the code so that scenario 1 would come into play with this data.

data have;
input USUBJID $ VISIT $ VISITNUM AVISIT $ AVISITN PARAM $ AVAL AWTARGET ADY AWDIFF;
datalines;
101 WEEK1 25 Week1 20 PAR1 55 5 4 1
101 WEEK1 25 Week1 20 PAR1 50 5 3 2
102 WEEK1 25 Week1 20 PAR1 55 5 4 1
102 WEEK1 25 Week1 20 PAR1 50 5 4 1
103 UNS 20.01 Week1 20 PAR1 55 5 5 0
103 UNS 20.02 Week1 20 PAR1 50 5 5 0
103 UNS 20.03 Week1 20 PAR1 50 5 5 0
105 Week20 20.01 Week1 20 PAR1 55 141 169 28
105 UNS20.02 20.02 Week1 20 PAR1 50 141 169 28 
105 Week44 20.03 Week1 20 PAR1 50 5 5 0 
;
run;

proc sort data=have; by usubjid param avisitn descending AWDIFF; run;

data want;
set have;
length DTYPE $40;
call missing(dtype);
by usubjid param avisitn descending AWDIFF;
*Define help vars;
retain n . countflg . avgn . avgsum . avgcalc . avisitnchg .;
*SC1;
if first.AWDIFF then countflg=0;
countflg+1;
if last.avisitn and countflg=1 then flg='Y';

*SC2/3;
lag1_avisitn=lag1(avisitn);
if first.avisitn then do;
   avgn=0;
   avgsum=0;
   avgcalc=0;
   avisitnchg=0;
   lag1_avisitn=.;
end;

avgn+1;
if avisitn ne lag1_avisitn then avisitnchg+1;
if not missing(aval) then avgsum=sum(avgsum,aval);
avgcalc=avgsum/avgn;

if last.avisitn and avgn>1 and countflg=avgn and missing(flg) then do;
   output;   
   *SC2/3;
   avg=avgcalc;
   dtype='AVG';
   *SC3;
   if avisitnchg=avgn then avisitn=999;
   output;
end;
else output;
drop n countflg lag1_avisitn avgn avgsum avgcalc avisitnchg;
run;