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

Help with Retain Step

Reply
Occasional Contributor
Posts: 7

Help with Retain Step

Hello All

 

I have input data looking like this.

 

ID VAL

1  8

1  9

1  16

2  10

2  17

2  12

3  11

3  11

4  12

4   24

 

DESIRED OUTPUT:

 

ID VAL NB

1  8      1        

1  9       1.125 

1  16     2        

2  10     1        

2  17     1.7

2  12      1.2

3  11      1

3  55       5

4  12       1

4   24      2

 

In other words, for the first variable of ID, retain VAL and use it to divide with the VAL value until the next first ID variable.

For ID of 1, retain the first VAL (8) and use it to divide subsequent VAL values and create NB. In this case, (8/8=1), (9/8=1.125), (16/8=2) etc.

 

Thanks

 

Trusted Advisor
Posts: 1,228

Re: Help with Retain Step

Posted in reply to VarmaSekhar

data want;
set have;
by id;
retain denominator;
if first.id then denominator=val;
nb=val/denominator;
run;

Super User
Posts: 5,434

Re: Help with Retain Step

Posted in reply to VarmaSekhar

@stat_sas provided a solid solution.

But I might ask what is the business rule behind this calculation? How can you be sure that the first row contains the denominator, I can't see any other variable to specify the order, such as time or sequence no.

Or is this just a practice?

Data never sleeps
Super User
Posts: 11,343

Re: Help with Retain Step

Posted in reply to VarmaSekhar

Is there any chance that VAL is less than the first value for each ID group? What would the result look like?

Ask a Question
Discussion stats
  • 3 replies
  • 333 views
  • 0 likes
  • 4 in conversation