Creating flag and additional record

[ambadi007]

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

[Oligolas]

what should the avisitn be for scenario 3?

[ambadi007]

We can give as 999

[ambadi007]

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

[PaigeMiller]

---

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

[ambadi007]

The record which is having low awdiff will be close value

[PaigeMiller]

---

@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

[ambadi007]

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

[Oligolas]

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;

[ambadi007]

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.

[Oligolas]

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;