topic descending sequence number, considering same number in SAS Programming

descending sequence number, considering same number

Alexxxxxxx — Thu, 28 May 2020 23:34:15 GMT

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

I expect to get table 2 like

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

Re: descending sequence number, considering same number

novinosrin — Thu, 28 May 2020 23:50:21 GMT

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

Re: descending sequence number, considering same number

yabwon — Fri, 29 May 2020 06:51:58 GMT

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

Re: descending sequence number, considering same number

Alexxxxxxx — Fri, 29 May 2020 08:09:34 GMT

many thanks!

Re: descending sequence number, considering same number

Alexxxxxxx — Fri, 29 May 2020 08:11:21 GMT

Many thanks for your advice.

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

Re: descending sequence number, considering same number

yabwon — Fri, 29 May 2020 09:18:18 GMT

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

Re: descending sequence number, considering same number

Alexxxxxxx — Fri, 29 May 2020 12:41:17 GMT

Many thanks for your answer.