topic Re: RETAIN statement and iteration in SAS Data Management

RETAIN statement and iteration

JOLSAS — Mon, 03 Mar 2014 06:41:56 GMT

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

Re: RETAIN statement and iteration

MichelleHomes — Mon, 03 Mar 2014 07:30:34 GMT

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

Re: RETAIN statement and iteration

Kurt_Bremser — Mon, 03 Mar 2014 07:46:36 GMT

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;

Re: RETAIN statement and iteration

JOLSAS — Mon, 03 Mar 2014 08:11:50 GMT

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

Re: RETAIN statement and iteration

MichelleHomes — Mon, 03 Mar 2014 08:12:01 GMT

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

Cheers,

Michelle