calculation percent change based on last higher percentage value

[marneni02]

Hi experts,

Please apologize for any typos.

I have the below dataset and I am trying to calculate the percent and flag. The first record of the subject will be calculated based on ((w2- w1)/w2)*100 and the same calculation will be carried out till any visit has percent +/-10%. If any visit (ascending order) has +/-10% then the later visits percent will be calculated based on that w2 value. Also we need flag the records where this change takes place.

Have this dataset:

data have;

infile datalines dsd delimiter = ' ' ;

input sub $ visit $ w1  w2 ;

datalines;

001 Visit1 50 54

001 Visit2 50 57

001 Visit3 50 56

001 Visit4 50 51

001 Visit5 50 55

001 Visit6 50 54

002 Visit1 45 52

002 Visit2 45 48

002 Visit3 45 49

002 Visit4 45 46

002 Visit5 45 50

;

run;

and want this dataset

001 Visit1 50 54 8 

001 Visit2 50 57 14      Y

001 Visit3 50 56 -1.7

001 Visit4 50 51 -10.5   Y

001 Visit5 50 55 7.8

001 Visit6 50 54 5.8

002 Visit1 45 52 15.5    Y

002 Visit2 45 48 -7.6

002 Visit3 45 49 -5.7

002 Visit4 45 46 -11.5   Y

002 Visit5 45 50 8.6

In want dataset, sub-001 has Visit1, Visit2 percent calculated based on W1. Due to the percent value +/-10% at Visit2 the later visits Visit3, Visit4 percent calculated based on Visit2 w2 value.

Due to the Visit4 has +/-10% the later visits from there will be calculate based on Visit4 W2 value. 

Can you please help me on this.

[PaigeMiller]

---

@marneni02 wrote:

The first record of the subject will be calculated based on ((w2- w1)/w2)*100 and the same calculation will be carried out till any visit has percent +/-10%. If any visit (ascending order) has +/-10% then the later visits percent will be calculated based on that w2 value.

---

When you talk about +/- 10%, do you really mean abs(percent) >= 10 (where percent = 100*(w2-w1)/w2) ? Or do you mean something different?

[marneni02]

Thank you so much for checking on the post.

Yes, correct. I mean abs(percent) >= 10.

[Kurt_Bremser · Accepted solution]

Based on your expeted results, I take it that the formula should be:

(w2 - w1) / w1

so the code would be, by retaining the w1 value:

data have;
infile datalines dsd delimiter = ' ' ;
input sub $ visit $ w1  w2 ;
datalines;
001 Visit1 50 54
001 Visit2 50 57
001 Visit3 50 56
001 Visit4 50 51
001 Visit5 50 55
001 Visit6 50 54
002 Visit1 45 52
002 Visit2 45 48
002 Visit3 45 49
002 Visit4 45 46
002 Visit5 45 50
;

data want;
set have;
by sub;
retain _w1;
if first.sub then _w1 = w1;
pct = (w2 - _w1) / _w1;
format pct percent7.2;
if abs(pct) > .1
then do;
  _w1 = w2;
  flag = 'Y';
end;
drop _w1;
run;

[marneni02]

I sincerely appreciate your help, this is worked.