Search code examples
sascountdown

SAS reverse count within ID group

I am used to creating count variables within a group where the count goes upwards +1 at each time using :

data objective ;
set eg ;
  count + 1 ;
  by id age ;
  if first.age then count = 1 ;
run ;

However I would like to do the reverse, i.e. where the first value of age in each id group has a value of 10 and each subsequently line has a value of -1 that of the preceding line:

data eg ;
  input id age desire ;
  cards;
  1 5 10
  1 4 9
  1 3 8
  1 2 7
  1 1 6
  2 10 10
  2 9 9
  2 8 8
  2 7 7
  2 6 6
  2 5 5
  2 4 4
  2 3 3
  2 2 2
  2 1 1
  3 7 10
  3 6 9
  3 5 8
  3 4 7
  3 3 6
  3 2 5
  3 1 4
  ;
run;

data objective ;
set eg ;
  count - 1 ;
  by id age ;
  if first.age_ar then count = 10 ;
run ;

Is there a way to do this as count-1 is not recognised.

Solution

  • You can add -1 without using retain as follows:

    data objective;
    set eg;
    count + -1;
    by id descending age;
    if first.id then count = 10;
run;