How can I implement rank function in Proc sql?

[ven]

How can I implement rank function in SAS Proc sql which will give below output?

GroupNo Rank

1               1   

1               2   

1               3

2               1

2               2

3               1   

3               2

3               3
   

I need to use it in SAS DI without using custom coding, which will happen if I didn't use proc sql( or let me know if it can be done in another way in SAS DI by using metadata only). Yet there is no simple proc sql implementation guess will have to use the data step approach. Thanks all.

[Kurt_Bremser]

The SAS RANK function (SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition) returns the position of a character in the collating sequence.

I guess you meant something else? Please clarify your intentions.

[jakarman]

The rank function of oracle/ms Ranking Functions (Transact-SQL) does not exist in SAS (ansi-sql 99). It is the 2013 ansi version  Select (SQL) - Wikipedia, the free encyclopedia) that got introduced
You example is showing a grouped in a  window. As SAS is an analytical system those analytics have been solves al long time before that by the statistical procs supporting by/class statements.

Call those "sas procs" pacakages for modernizing the words not the meanings.

[user24feb]

I believe you could mean this (count by group) 😉

Data Have;
  Input Nr @@;
  Datalines;
1 1 1 2 2 3 3 3 4 4 4 4 5 5 5
;
Run;

Proc SQL;
  Create Table Want As
  Select a.Nr, Monotonic()-b.Min_Grp+1 As Rank From Have a
    Left Join (Select Nr, Min(Monotonic()) As Min_Grp From Have Group By Nr) b
   On a.Nr eq b.Nr;
Quit;

[Kurt_Bremser]

And in a data step:

data want;

set have;

by nr;

if first.nr

then rank = 1;

else rank + 1;

run;

[jakarman]

Would avoid the monotonic function.  It is an undocumented feature and most likey not reliable with the multi-threading capabilities

Processing the data sequential (data-step) is the most easy one and will perform well even with bigger datasets.

Using the reporting and statistical procs is a better approach when the logical question is the appropriate fit.
Recoding Excel SQL or Cobol into SAS is just a bad idea.

[Steelers_In_DC]

data want;

set have;

by groupno;

rank +1;

if first.groupno then rank = 1;

run;

[AlokBishnoi]

DATA D0;

INPUT GroupNo;

DATALINES;

1

1

1

2

2

3

3

3

;

/* You can skip sorting if data is already sorted */

proc sort data=d0; by GroupNo; run;

/* You can put any other formated variable than A but keep in mind that a formated value should not be equal to actual value of the variable in any of the observation */

proc sql;

create table d0_fin(DROP=A)  as

select PUT(GroupNo,Z5.) AS A, GroupNo,

(monotonic() - min(monotonic())+1)  as Rank

from d0

group by a

order by a, monotonic();

quit;

[adi121]

Can anyone tell me how (monotonic() - min(monotonic())+1)  this is working and assigning the rank as 1,2,..so on .

Monotonic will give 1,2,3, and so on and min(monotonic) is 1 so i dont get how it is calculating the rank on which basis??

1-1+1=1

2-1+1=2

is this the way it is working??