BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Pysiek
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

8 REPLIES 8
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Pysiek
Obsidian | Level 7

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
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Pysiek
Obsidian | Level 7
It worked ! Many thanks 🙂
Pysiek
Obsidian | Level 7

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
Onyx | Level 15

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;
_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Pysiek
Obsidian | Level 7
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
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 8 replies
  • 1175 views
  • 2 likes
  • 2 in conversation