How to check if all values are increasing/decreasing.

[Pysiek]

Hi guys,

I have small problem. Below you can see example data for one subject. This subject has 7 visits. I need to compare AVAL with BASE and calculate change from baseline (this is already done). Then when change from baseline is >= 10 I should flag this record and check if all subsequent visits are also increasing comparing to baseline, If YES then V6 should be flag with "Y". If there will be any visit after V6 where AVAL < BASE (40) then flag from V6 will be set to blank (in example 2 on V10 AVAL is = 39 so flag on V6 shouldn't be assigned). Can anyone help me and give small explanation how I can deal with that?

Example 1 )

VISIT AVAL BASE CHANGE

V1      40     40           0
V3      49     40           9
V5      49     40           9
V6      50     40           10 (FLAG as "Y" because chg >= 10)
V8      60     40           20
V9      65     40           25
V10    66     40           26

Example 2 )

VISIT AVAL BASE CHANGE

V1      40     40           0
V3      49     40           9
V5      49     40           9
V6      50     40           10 (FLAG not assigned because there is AVAL = 39 on V10)
V8      60     40           20
V9      65     40           25
V10    39     40           -1

[yabwon]

Something like this:

data A;
subj = "A";
input VISIT $ AVAL BASE CHANGE;
cards;
V1      40     40           0
V3      49     40           9
V5      49     40           9
V6      50     40           10  Y
V8      60     40           20
V9      65     40           25
V10    66     40           26
;
run;
 
data B;
subj = "B";
input VISIT $ AVAL BASE CHANGE;
cards;
V1      40     40           0
V3      49     40           9
V5      49     40           9
V6      50     40           10 N
V8      60     40           20
V9      65     40           25
V10    39     40           -1
;
run;

data C;
subj = "C";
input VISIT $ AVAL BASE CHANGE;
cards;
V1      40     40           0
V3      49     40           9
V5      49     40           9
V6      50     40           10 N because the last change is smaller than previous (21<25)
V8      60     40           20
V9      65     40           25
V10    61     40           21
;
run;

data D;
subj = "D";
input VISIT $ AVAL BASE CHANGE;
cards;
V1      50     40           10 Y
V8      60     40           20
V9      65     40           25
V10    70     40           30
;
run;

data have;
  set A B C D;
run;


data want;
  m = 0;
  do _N_=1 by 1 until(last.subj);
    set have;
    by subj;
    k = lag(CHANGE);

    if first.subj then k = .; /* in case change>=10 is the first */
    if m = 0 and CHANGE >= 10 then m = _N_; /* makrk first occurence of change>=10*/
    if m and CHANGE < k then m = -1; /* check if decreases (current CHANGE is less then previous)*/
  end;

  do _N_=1 to _N_;
    set have;
    if m = _N_ then FLAG = "Y";
               else FLAG = " ";
    output;
  end;

  drop m k;
run;
proc print;
run;

?

Bart

[Pysiek]

Hi Bart,

Thanks for your reply. I don't really need to check if change was smaller than previous. Only find first CHANGE >= 10 and then check if all other records are also increasing comparing to baseline.

[yabwon · Accepted solution]

in that case it's even simpler.

data want;
  m = 0;
  do _N_=1 by 1 until(last.subj);
    set have;
    by subj;

    if m = 0 and CHANGE >= 10  then m = _N_; /* makrk first occurence of change>=10 */
    if m > 0 and AVAL < BASE then m = -1 ; /* check AVAL < BASE for records over CHANGE >= 10 */
  end;

  do _N_=1 to _N_;
    set have;
    if m = _N_ then FLAG = "Y";
               else FLAG = " ";
    output;
  end;

  drop m;
run;

B

[Pysiek]

It worked ! Many thanks 🙂

[Pysiek]

Hi Bart,

I've got one more scenario to cover. I have to check CHANGE like you covered in your first solution. In below example there was increase >= 10 at visit V3 but then we had two visits with decrease (V5, V6). On visit 8 there was another CHANGE >=10 which was followed with two visits without decrease. I would like to flag VISIT = 8 in this scenario. Can you help me?

data D;
subj = "D";
input VISIT $ AVAL BASE CHANGE;
cards;
V1 8 8 0
V3 25 8 16 Y
V5 16 8 8
V6 16 8 8
V8 25 8 16 Y
V9 25 8 16 Y
V10 25 8 16 Y

[yabwon]

Seems to mi that this will do, but please check out example C, ok?

Bart

data A;
subj = "A";
input VISIT $ AVAL BASE CHANGE;
cards;
V1      40     40           0
V3      49     40           9
V5      49     40           9
V6      50     40           10  Y
V8      60     40           20
V9      65     40           25
V10    66     40           26
;
run;
 
data B;
subj = "B";
input VISIT $ AVAL BASE CHANGE;
cards;
V1      40     40           0
V3      49     40           9
V5      49     40           9
V6      50     40           10 N
V8      60     40           20
V9      65     40           25
V10    39     40           -1
;
run;

data C;
subj = "C";
input VISIT $ AVAL BASE CHANGE;
cards;
V1      40     40           0
V3      49     40           9
V5      49     40           9
V6      50     40           10 N because the last change is smaller than previous (21<25)
V8      60     40           20
V9      65     40           25
V10     61     40           21 Y
;
run;

data D;
subj = "D";
input VISIT $ AVAL BASE CHANGE;
cards;
V1      50     40           10 Y
V8      60     40           20
V9      65     40           25
V10    70     40           30
;
run;

data E;
subj = "E";
input VISIT $ AVAL BASE CHANGE;
cards;
V1 8 8 0
V3 25 8 16 Y
V5 16 8 8
V6 16 8 8
V8 25 8 16 Y
V9 25 8 16 Y
V10 25 8 16 Y
;
run;

data have;
  set A B C D E;
run;

data want;
  m = 0;
  do _N_=1 by 1 until(last.subj);
    set have;
    by subj;
    k = lag(CHANGE);

    if first.subj then k = .; /* in case change>=10 is the first */
    if m > 0  and CHANGE < k then m = -1; /* check if decreases (current CHANGE is less then previous)*/
    if m <= 0 and CHANGE >= 10 then m = _N_; /* makrk first occurence of change>=10*/
  end;

  do _N_=1 to _N_;
    set have;
    if m = _N_ then FLAG = "Y";
               else FLAG = " ";
    output;
  end;

  drop m k;
run;
proc print;
run;

[Pysiek]

Hi Bart,
I think I was not clear enough. If there was first increase with CHANGE >= 10 I need to make sure if next obs are also increasing >=10 (comparing to baseline not to previous observation).

[yabwon]

in that case this should do the job:

data A;
subj = "A";
input VISIT $ AVAL BASE CHANGE;
cards;
V1      40     40           0
V3      49     40           9
V5      49     40           9
V6      50     40           10  Y
V8      60     40           20
V9      65     40           25
V10    66     40           26
;
run;
 
data B;
subj = "B";
input VISIT $ AVAL BASE CHANGE;
cards;
V1      40     40           0
V3      49     40           9
V5      49     40           9
V6      50     40           10 N
V8      60     40           20
V9      65     40           25
V10    39     40           -1
;
run;

data C;
subj = "C";
input VISIT $ AVAL BASE CHANGE;
cards;
V1      40     40           0
V3      49     40           9
V5      49     40           9
V6      50     40           10 N because the last change is smaller than previous (21<25)
V8      60     40           20
V9      65     40           25
V10     61     40           21 Y
;
run;

data D;
subj = "D";
input VISIT $ AVAL BASE CHANGE;
cards;
V1      50     40           10 Y
V8      60     40           20
V9      65     40           25
V10    70     40           30
;
run;

data E;
subj = "E";
input VISIT $ AVAL BASE CHANGE;
cards;
V1 8 8 0
V3 25 8 16 Y
V5 16 8 8
V6 16 8 8
V8 25 8 16 Y
V9 25 8 16 Y
V10 25 8 16 Y
;
run;

data have;
  set A B C D E;
run;

data want;
  m = 0;
  do _N_=1 by 1 until(last.subj);
    set have;
    by subj;
    
    if m <= 0 and CHANGE >= 10  then m = _N_; /* makrk first occurence of change>=10 */
    if m >  0 and AVAL < BASE then m = -1 ; /* check AVAL < BASE for records over CHANGE >= 10 */  
    if CHANGE < 10 then m = 0;
  end;

  do _N_=1 to _N_;
    set have;
    if m = _N_ then FLAG = "Y";
               else FLAG = " ";
    output;
  end;

  drop m k;
run;
proc print;
run;

Bart