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,204

Re: Help with Retain Step

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

Super User
Posts: 5,255

Re: Help with Retain Step

@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: 10,466

Re: Help with Retain Step

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
  • 332 views
  • 0 likes
  • 4 in conversation