ART:
Thanks for your time and sharing your solution to my post.
Here is SAS pgm. I ran and output is displayed below.
data one;
input ID GRP AMT ;
cards;
0 1 10
0 2 20
1 1 10
1 2 20
2 1 25
2 2 50
2 3 75
2 3 100
3 1 51
3 2 61
3 3 71
3 3 81
3 4 91
4 1 20
4 2 40
4 3 60
4 3 80
4 4 100
4 5 120
4 6 140
;
run;
data want (drop=last:);
set one;
array last_amt(50);
retain last:;
if id eq 0 or missing(last_amt(grp)) then new_amt=amt;
else new_amt=abs(amt-last_amt(grp));
last_amt(grp)=amt;
run;
>>>>>>> OUTPUT>>>>>>>>>>>>>>
Obs ID GRP AMT new_amt
1 0 1 10 10
2 0 2 20 20
3 1 1 10 0
4 1 2 20 0
5 2 1 25 15
6 2 2 50 30
7 2 3 75 75
8 2 3 100 25
9 3 1 51 26
10 3 2 61 11
11 3 3 71 29
12 3 3 81 10
13 3 4 91 91
14 4 1 20 31
15 4 2 40 21
16 4 3 60 21
17 4 3 80 20
18 4 4 100 9
19 4 5 120 120
20 4 6 140 140
.........
For record # 7, it correctly calculated NEW_AMT. We are substracting 75(ID=2,GRP=3) minus 0( there is no ID=1 and GRP=3).
For record # 8, NEW_AMT should be 100. We are subtracting 100(ID=2,GRP=3) minus 0(there is no ID=1 and GRP=3).
For record # 11, NEW_AMT should be -4. We are subtracting 71( where ID=3 & GRP=3) from 75 (where ID=2 and GRP=3).
For record # 12, NEW_AMT should be -19. We are subtracting 81( where ID=3 & GRP=3) from 100 (where ID=2 and GRP=3).
For record # 13, it calculated correclty NEW_AMT=91.
For record # 14, NEW_AMT should be -31. We are subtracting 20( where ID=4 & GRP=1) from 51 (where ID=3 and GRP=1).
For record # 15, NEW_AMT should be -21. We are subtracting 40( where ID=4 & GRP=2) from 61 (where ID=3 and GRP=2).
For record # 16, NEW_AMT should be -11. We are subtracting 60( where ID=4 & GRP=3) from 71 (where ID=3 and GRP=3).
For record # 17, NEW_AMT should be -1. We are subtracting 80( where ID=4 & GRP=3) from 81 (where ID=3 and GRP=3).
For record # 18, calculated NEW_AMT is correct.
In nut shell, the rule is
For ID > 0,
SUBTRACT VALUE OF GIVEN (ID,GRP) MINUS VALU EOF PREVIOUS (ID,GRP).
If for the current record, for the given value of (ID,GRP), if previous value of (ID,GRP) doesn't exist, then for the current record, new_amt should be the same as amt.
I hope this explains rules.
Thanks for your assistance.
... View more
