Calculate on subgroup and output need to be cumulated and reconsider for calculation.

[rahulramesh3321]

Hi Everyone,

I am stuck in one problem where I am not able to program it. Sample data is given below : 

ID	A1x	B1y		Output
AA	87500000	1750000000		87500000
AA	0	628420515.9		31421025.8
AA	0	603509371		30175468.55
BB	40,000	800,000		40,000
BB	0	720,000		36000
CC	440,250	8,805,000		440,250
CC	0	7,885,000		394,250
CC	0	6,458,720		322,936
CC	0	2,571,356		128,567.80

Output Calculation : 

For AA

OBS         output

1               87500000

2               (87500000/1750000000)*628420515.9 = 31421025.8

3               (87500000 +31421025.8 /1750000000 + 628420515.9) * 603509371 = 30175468.55

For CC

OBS        Output

1             440,250

2             (440,250 / 8,805,000)*7,885,000 = 394,250

3              (440,250 + 394,250/ 8,805,000 +7,885,000)*6,458,720 = 322,936

4              (440,250 + 394,250+322,936/ 8,805,000 +7,885,000+6,458,720) * 2,571,356 = 128,567.80

I have tried with using lag and cumulative but not able to go beyond obs =2 and also got issue in implementing in the subgroup.

I had a thought of using array and loop but it was making program quite complicated and also not getting the desired output.

Please help.

[lalaktgrau]

hey, can you create a dataset using DATALINES, so I can play with it in SAS?

[rahulramesh3321]

Sure,

data test;
infile datalines delimiter=',';
input ID$ A1x B1y;
datalines;
AA,87500000,1750000000
AA,0,628420515.9
AA,0,603509371
BB,40000,800000
BB,0,720000
CC,440250,8805000
CC,0,7885000
CC,0,6458720
CC,0,2571356
;

run;