BookmarkSubscribeRSS Feed
ambadi007
Quartz | Level 8

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

 

 

 

10 REPLIES 10
Oligolas
Barite | Level 11

what should the avisitn be for scenario 3?

________________________

- Cheers -

ambadi007
Quartz | Level 8
We can give as 999
ambadi007
Quartz | Level 8
could you please help me on this .. its little bit urgent now
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
ambadi007
Quartz | Level 8
The record which is having low awdiff will be close value
PaigeMiller
Diamond | Level 26

@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

--
Paige Miller
ambadi007
Quartz | Level 8
yes for the first scenario we have to take the lowest value
Oligolas
Barite | Level 11

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;
________________________

- Cheers -

ambadi007
Quartz | Level 8

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
Barite | Level 11

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;
________________________

- Cheers -

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1163 views
  • 0 likes
  • 3 in conversation