SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

RETAIN statement and iteration

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

RETAIN statement and iteration

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


Accepted Solutions
Solution
‎03-03-2014 02:46 AM
Super User
Posts: 6,936

Re: RETAIN statement and iteration

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Trusted Advisor
Posts: 1,247

Re: RETAIN statement and iteration

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

Solution
‎03-03-2014 02:46 AM
Super User
Posts: 6,936

Re: RETAIN statement and iteration

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,247

Re: RETAIN statement and iteration

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

Cheers,

Michelle

Contributor
Posts: 45

Re: RETAIN statement and iteration

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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