DATA Step, Macro, Functions and more

Calculate a difference from "baseline" defined by multi variables in a loop

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Calculate a difference from "baseline" defined by multi variables in a loop

Dear Community, 

 

I have the following dataset, that describes N persons who have up to 3 visits and took 3 measures (vars) at each visit. 

 

Person     Visit   Var1 Var2 Var3   

1               1      10    30     1    

1               2       5     20     3 

2               1       7     15     5     

2               2       5      13    5    

3               3       5      13     5       

3               1       6      12    4         

3               2       1      10    7     

3               3       2       8    6      

 

I would like to calculate a difference from the first visit for each Var. The resulting dataset should look like this: 

 

Person     Visit   Var1 Var2 Var3    Diff_1     Diff2    Diff3

1               1      10    30     1          .            .           .

1               2       5     20     3 ..        -5          -10       2

2               1       7     15     5          .           .           . 

2               2       5      13    5          -2         -17       0

3               3       5      13     5         .             .          .

3               1       6      12    4         1            -1         -1

3               2       1      10    7         -4          -3          2

3               3       2       8    6          -3          -5          1

 

I created the following code: 

-----------------------------------------------------------------

PROC SORT DATA = have;
      by Person VISIT;
RUN;

 

DATA want;
    SET have;
    by Person VISIT;
    RETAIN BL; //Baseline value = value for the first visit

ARRAY ITEM{3} VAR1 - VAR3;
ARRAY ITEM_sinceBL{3} Var1_sinceBL Var2_sinceBL Var3_sinceBL;


DO I=1 TO 3;
    if first.Person THEN BL = ITEM(I);
    ELSE ITEM_sinceBL(I) = ITEM(I) - BL;
END;


drop BL;
RUN;

––––––––––––––––––––––––––––––––––––––––––

 

(I use look because in reality I have 18 variables.) But I don't get the right result. My BL seems to have the wrong value. 

 

I would appreciate your help very much

 

Julia


Accepted Solutions
Solution
‎01-30-2018 02:34 PM
Super User
Super User
Posts: 9,416

Re: Calculate a difference from "baseline" defined by multi variables in a loop

DONT CODE ALL IN UPPERCASE!

It hurts the eyes.

 

So what you want to do is:

get baseline record, merge this onto your data, then calculate.

data want;
  merge have 
        have (where=(visit=1) rename=var1-var_old1 var2=var_old2 var3=var_old3);
by person; array var{3}; array var_old{3}; array diff_{3} 8.; do i=1 to 3; diff_{i}=var{i} - var_old{i}; end; run;

Not tested, post test data in the form of a datastep so we have something to work with.

View solution in original post


All Replies
Super Contributor
Posts: 331

Re: Calculate a difference from "baseline" defined by multi variables in a loop

Hello,

When you're on the baseline row, you set the BL variable three times so that, on the following rows you're only left with the last computed BL value, BL=ITEM(3).
You have to store baseline values in an array.
Solution
‎01-30-2018 02:34 PM
Super User
Super User
Posts: 9,416

Re: Calculate a difference from "baseline" defined by multi variables in a loop

DONT CODE ALL IN UPPERCASE!

It hurts the eyes.

 

So what you want to do is:

get baseline record, merge this onto your data, then calculate.

data want;
  merge have 
        have (where=(visit=1) rename=var1-var_old1 var2=var_old2 var3=var_old3);
by person; array var{3}; array var_old{3}; array diff_{3} 8.; do i=1 to 3; diff_{i}=var{i} - var_old{i}; end; run;

Not tested, post test data in the form of a datastep so we have something to work with.

Contributor
Posts: 32

Re: Calculate a difference from "baseline" defined by multi variables in a loop

Thank you very much! It worked for me.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 139 views
  • 1 like
  • 3 in conversation