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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.