RETAIN statement and iteration

[JOLSAS]

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

[MichelleHomes]

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

[Kurt_Bremser · Accepted solution]

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]

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

Cheers,

Michelle

[JOLSAS]

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