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

rahulramesh3321 — Wed, 17 Oct 2018 11:11:23 GMT

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.

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

lalaktgrau — Wed, 17 Oct 2018 14:53:07 GMT

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

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

rahulramesh3321 — Fri, 19 Oct 2018 08:49:57 GMT

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;

topic Re: Calculate on subgroup and output need to be cumulated and reconsider for calculation. in SAS Programming

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

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

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