compute cumulative product by id

Solved
Frequent Contributor
Posts: 130

compute cumulative product by id

[ Edited ]

I am trying to compute cumulative product for variable y, x, z by id, how to make following code works properly? thanks!

the following code computes correctly for id=aa, but is wrong for id=bb. I would expect that computation for id=bb starts from bb's first observation, rather than continue from aa's last observation.

``````data have;
input id \$2. month y x z;
datalines;
aa 1 0.5 0.1 0.8
aa 2 3 3 3
aa 3 2 2 2
aa 4 3 3 3
aa 5 2 2 2
aa 6 1 1 1
aa 7 2 2 2
aa 8 2 2 2
aa 9 2 2 2
aa 10 3 3 3
bb 1 5 1 8
bb 2 3 3 3
bb 3 2 2 2
bb 4 3 3 3
bb 5 2 2 2
bb 6 1 1 1
bb 7 2 2 2
bb 8 2 2 2
bb 9 2 2 2
bb 10 3 3 3
;
run;

data want;
set have;
by id;
retain yproduct 1 xproduct 1 zproduct 1;
yproduct = yproduct*y;
xproduct = xproduct*x;
zproduct = zproduct*z;
run;``````

Desired output looks like:

Accepted Solutions
Solution
‎01-19-2017 12:37 AM
Super Contributor
Posts: 268

Re: compute cumulative product by id

Thank you - it is easier if we know what we're aiming at!

The code is simpler - no if last.id and a modification to the keep statement.

``````data want;
set have;
by id;
array products[*] yproduct xproduct zproduct;
retain yproduct xproduct zproduct;
if first.id then do i = 1 to dim(products);  /* Initialise products at start of id */
products[i] = 1;
end;
yproduct = yproduct * y;
xproduct = xproduct * x;
zproduct = zproduct * z;
keep id x y x yproduct xproduct zproduct;
run;``````

All Replies
Super Contributor
Posts: 268

Re: compute cumulative product by id

Assuming you just want one row per group (2!), is this it?

``````data want;
set have;
by id;
array products[*] yproduct xproduct zproduct;
retain yproduct xproduct zproduct;
if first.id then do i = 1 to dim(products);  /* Initialise products at start of id */
products[i] = 1;
end;
yproduct = yproduct * y;
xproduct = xproduct * x;
zproduct = zproduct * z;
if last.id;                            /* Only output the last row for each id */
keep id yproduct xproduct zproduct;
run;``````
Frequent Contributor
Posts: 130

Re: compute cumulative product by id

Thank you LaurieF for your suggestion, I just update my desired outputs.

Solution
‎01-19-2017 12:37 AM
Super Contributor
Posts: 268

Re: compute cumulative product by id

Thank you - it is easier if we know what we're aiming at!

The code is simpler - no if last.id and a modification to the keep statement.

``````data want;
set have;
by id;
array products[*] yproduct xproduct zproduct;
retain yproduct xproduct zproduct;
if first.id then do i = 1 to dim(products);  /* Initialise products at start of id */
products[i] = 1;
end;
yproduct = yproduct * y;
xproduct = xproduct * x;
zproduct = zproduct * z;
keep id x y x yproduct xproduct zproduct;
run;``````
Frequent Contributor
Posts: 130

Re: compute cumulative product by id

Thank you LaurieF!

Super Contributor
Posts: 268

Re: compute cumulative product by id

It was a pleasure. by-group processing is one of the most powerful components of a data step. Other languages handle the same concept differently (or not at all!), but SAS's method is so elegant.

☑ This topic is solved.