BookmarkSubscribeRSS Feed
GPatel
Pyrite | Level 9
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

..........
..........
..........
5 REPLIES 5
art297
Opal | Level 21
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
GPatel
Pyrite | Level 9
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.
art297
Opal | Level 21
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.
GPatel
Pyrite | Level 9
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
art297
Opal | Level 21
>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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 851 views
  • 0 likes
  • 2 in conversation