topic Re: compute cumulative product by id in SAS Programming

compute cumulative product by id

Jonate_H — Thu, 19 Jan 2017 04:41:28 GMT

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:

Re: compute cumulative product by id

LaurieF — Thu, 19 Jan 2017 04:31:34 GMT

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;

Re: compute cumulative product by id

Jonate_H — Thu, 19 Jan 2017 04:43:54 GMT

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

Re: compute cumulative product by id

LaurieF — Thu, 19 Jan 2017 04:47:48 GMT

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;

Re: compute cumulative product by id

Jonate_H — Thu, 19 Jan 2017 05:39:31 GMT

Thank you LaurieF!

Re: compute cumulative product by id

LaurieF — Thu, 19 Jan 2017 05:41:36 GMT

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.