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

Hi all,

I'm having some difficulties with the retain statement and hope to get help from y'all.

I have a data set that contains unique ID and years, as well as a return variable. Basically it looks like this:

ID     Year     Return

1     2010     1.03

1     2011     0.98

1     2012     1.05

2     2009     1.06

2     2010     0.89

3     2011     1.12

3     2012     1.01

3     2013     1.10

.     .               .

.     .               .

.     .               .

What I want to get is to compute for each ID, the products of all the returns. For example, for ID 1: 1.03 * 0.98 * 1.05; for ID 2: 1.06 * 0.89; and for ID 3: 1.12*1.01*1.10.

The following is my code:

data want;

set have;

    by id;

    retain product 1;

    final = product * return;

    if last.id then output;

run;

The results I got is simply the last return data for each ID, instead of the products. It seems that SAS isn't doing iterations for me, so there must be something wrong with my code. Any ideas?

Any help is much appreciated! Thank you.

Jolynn

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Do this:

data want;

set have;

    by id;

    retain product;

    if first.id then product = 1;

    product = product * return;

    if last.id then output;

run;

View solution in original post

4 REPLIES 4
MichelleHomes
Meteorite | Level 14

Hi Jolynn,

In your code you retain product and initialize it to 1 but you aren't changing its value so it stays as 1. Final is a new variable you create that is 1 * return so it will always be the value of return for the last id value. Change your statement to:

    product = product * return;


Cheers,

Michelle

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
Kurt_Bremser
Super User

Do this:

data want;

set have;

    by id;

    retain product;

    if first.id then product = 1;

    product = product * return;

    if last.id then output;

run;

MichelleHomes
Meteorite | Level 14

Oops... Of course. A reset at the start of each id is needed. Good pick up Kurt.

Cheers,

Michelle

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
JOLSAS
Quartz | Level 8

Thanks guys, for staying up late at night and answering my question! I can sleep tight tonight now.

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1206 views
  • 3 likes
  • 3 in conversation