topic Re: Calculate New Field in SAS Procedures

Calculate New Field

GPatel — Sat, 16 Apr 2011 20:58:40 GMT

I have ID, GRP and AMT fields. Basically, what I want to calculate NEW_AMT field.

(A) When ID=0, the NEW_AMT field should be the same as AMT.

(B) For ID>0, WITHIN EACH ID, SUBTRACT AMT FOR EACH GRP FROM THE PREVIOUS GRP.
IF PREVIOUS GRP DOES NOT EXIST THEN AMT = NEW_AMT.

(C) If there is no AMT entry for the previous GRP
Then, NEW_AMT would be the same as AMT.

(D) ID &/or GRP can take vaule upto 50.

Thanks for your help

>>>>>

ID GRP AMT NEW_AMT

0 1 10 10
0 2 20 20

1 1 10 10-10=0
1 2 20 20-20=0

2 1 25 25-10 =15
2 2 50 50-20 =30
2 3 75 75-0 =75
2 3 100 100-0= 100

3 1 51 51-25= 26
3 2 61 61-50=11
3 3 71 71-75=-4
3 3 81 81-100 =-19
3 4 91 91-0=91

4 1 20 20-51=-31
4 2 40 40-61=-21
4 3 60 60-71=-11
4 3 80 80-81=-1
4 4 100 100-91=9
4 5 120 120-0=0
4 6 140 140-0=0

..........
..........
..........

Re: Calculate New Field

art297 — Sat, 16 Apr 2011 22:01:39 GMT

Either your rules or example data are incorrect or don't understand what you want to accomplish. The following comes close, but does not achieve all of the values you want. Hopefully, you can correct it yourself:

data want (drop=last:);
set have;
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;

Art

Re: Calculate New Field

GPatel — Sun, 17 Apr 2011 00:49:26 GMT

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.

Re: Calculate New Field

art297 — Sun, 17 Apr 2011 14:15:11 GMT

If the values for records 19 and 20 really should be 120 and 140, then the following might do what you want to accomplish:

data want (drop=last:);
set have;
array last_amt(50,50);
retain last:;
by id grp;
if first.grp then within_grp=1;
else within_grp+1;
if id eq 0 or missing(last_amt(grp,within_grp)) then new_amt=amt;
else new_amt=amt-last_amt(grp,within_grp);
last_amt(grp,within_grp)=amt;
run;

HTH,
Art
--------
> ART:
> Thanks for your time and sharing your solution to my
> post.
>
> ere 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;
>
> >>>>>>>>>>>
>
> 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.
>
> n 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.

Re: Calculate New Field

GPatel — Sun, 17 Apr 2011 22:40:00 GMT

Art,

It worked like a charm. Thanks for your prompt and perfect solution. Can you please explain me the logic " last_amt(grp,witin_grp) " ?

I never encountered such a ...........

Again thanks a lot.

GPatel

Re: Calculate New Field

art297 — Sun, 17 Apr 2011 23:41:17 GMT

>Can you please explain me the logic "last_amt(grp,witin_grp) " ?

Arrays can be multidimensional. As such, since your desired logic required looking at the last value held in the same position, I simply created a 50x50 matrix and accessed the value at the required position.

Art