BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jonate_H
Quartz | Level 8

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:

 

output.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
LaurieF
Barite | Level 11

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;

View solution in original post

5 REPLIES 5
LaurieF
Barite | Level 11

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;
Jonate_H
Quartz | Level 8

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

LaurieF
Barite | Level 11

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;
Jonate_H
Quartz | Level 8

Thank you LaurieF!

LaurieF
Barite | Level 11

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2913 views
  • 2 likes
  • 2 in conversation