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
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
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
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.
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
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
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;
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
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.