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:
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;
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;
Thank you LaurieF for your suggestion, I just update my desired outputs.
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;
Thank you LaurieF!
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.