descending sequence number, considering same number

[Alexxxxxxx]

Hi,

I want to create the descending sequence number of table 1, especially, if two number are equal, then they should be recorded with the same sequence number (just like two '90' are order 2 and two '88' are order 4). 

I tried the code like 

order =_N_;

but it cannot figure out the problem like "two '90' are recorded as 2 and two '88' are recorded as  4"

Could you please give me some suggestions about this?

table 1

number

100

90

90

88

88

87

I expect to get table 2 like

number	order
100	1
90	2
90	2
88	4
88	4
87	6

[novinosrin]

Hi @Alexxxxxxx  All you need is PROC RANK

data have;
input number;
cards; 
100
90
90
88
88
87
;
proc rank data=have out=want descending ties=low;
   var number ;
   ranks order;
run;

RESULTS

[Alexxxxxxx]

many thanks!

[yabwon · Accepted solution]

Assuming your data are already sorted, by-group processing can do the job:

data have;
input number;
cards; 
100
90
90
88
88
87
;
run;

data want;
  set have;
  by descending number;
  if first.number then order =_N_;
  retain order 0;
run;

All the best

Bart

[Alexxxxxxx]

Many thanks for your advice.

May I ask the logic behind the code? it looks simple but works very well.

[yabwon]

Hi,

it's the `by-group processing` which is very basic/fundamental concept in the data step processing (google it).

Assuming that your dataset is sorted BY the variable `number` you can use the `by` clause which makes SAS to "turn on" two additional special technical/temporary variables related to `number` which are `first.number` and `last.number`. They work in the following way:

- `first.number` gets value 1 if it is the first observation of given by-group determined by value of `number`, and 0 otherwise

- `last.number` gets value 1 if it is the last observation of given by-group determined by value of `number`, and 0 otherwise

e.g.

number first.number last.number
1 1 0
1 0 0
1 0 1
2 1 0
2 0 1
3 1 1

the `IF` condition is checking if we are in the first observation for a given group and in case "yes" variable order gets the value of the _N_ variable, since it is happening only in the first observation of the group you have to remember this value for subsequent observations in the group - that is why you need use the `retain` statement to prevent SAS from making the `order` variable missing after each iteration of the main loop.

All the best

Bart

[Alexxxxxxx]

Many thanks for your answer.