CODEWITHSUNDEEP
sas
Cardinal
Cardinal

Reputation: 228

sas change last value by group to first value

I want to change data of the form

id   value
1     1
1     1 
1     2  
2     7
2     7
2     7 
2     5
.     .
.     .
.     .

to 

id   value
1     1
1     1 
1     1  
2     7
2     7
2     7 
2     7
.     .
.     .
.     .

That is, the last value by group should be the first value by group. I have tried the following code 

data want;
set have;
by id;
last.value=first.value;
run;

But that didn't work. Could someone help me out?

Upvotes: 0

Views: 652

Answers (2)

mjsqu
mjsqu

Reputation: 5417

The problem here is that first.value and last.value:

  • Do not hold the actual value, they just tell you if an observation is the first or last in a BY-group
  • Cannot be assigned - last.value = is not valid syntax

Secondly, first.value and last.value only get set if the value variable is stated in the by statement. You should use first.id and last.id instead.

What we need to do here is:

  1. Check if we are looking at an observation that is the first in the BY-group based on id
  2. Keep the value of the value variable until the last id value is reached
  3. When we are looking at the last id value then set the value from step 1.

Alexey's answer covers the actual syntax required to do this. Here's what the first.id/last.id values look like. (You can always view them by adding put _all_; into your datastep):

id   value  first.id  last.id  tValue
1     1     1         0        1
1     1     0         0        1
1     2     0         1        1
2     7     1         0        7
2     7     0         0        7
2     7     0         0        7
2     5     0         1        7
.     .
.     .
.     .

Upvotes: 3

Llex
Llex

Reputation: 1770

You should save first.id value in variable and retain it.

data want(drop=tValue);
   set have;
   by id;
   retain tValue;
   if first.id then tValue=value;
   if last.id then value=tValue;
run;

Upvotes: 4

Related Questions