topic Re: How to check if all values are increasing/decreasing. in SAS Programming

How to check if all values are increasing/decreasing.

Pysiek — Thu, 21 Oct 2021 13:21:10 GMT

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

Re: How to check if all values are increasing/decreasing.

yabwon — Thu, 21 Oct 2021 14:39:25 GMT

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

Re: How to check if all values are increasing/decreasing.

Pysiek — Fri, 22 Oct 2021 08:08:50 GMT

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.

Re: How to check if all values are increasing/decreasing.

yabwon — Fri, 22 Oct 2021 09:36:45 GMT

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;

Re: How to check if all values are increasing/decreasing.

Pysiek — Mon, 25 Oct 2021 07:45:20 GMT

It worked ! Many thanks 🙂

Re: How to check if all values are increasing/decreasing.

Pysiek — Tue, 26 Oct 2021 14:37:33 GMT

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

Re: How to check if all values are increasing/decreasing.

yabwon — Tue, 26 Oct 2021 20:27:47 GMT

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;

Re: How to check if all values are increasing/decreasing.

Pysiek — Tue, 26 Oct 2021 21:23:49 GMT

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

Re: How to check if all values are increasing/decreasing.

yabwon — Wed, 27 Oct 2021 19:53:16 GMT

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