topic Re: How can I implement rank function in Proc sql? in SAS Procedures

How can I implement rank function in Proc sql?

ven — Mon, 04 May 2015 04:45:26 GMT

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.

Re: How can I implement rank function in Proc sql?

Kurt_Bremser — Mon, 04 May 2015 10:57:32 GMT

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.

Re: How can I implement rank function in Proc sql?

jakarman — Mon, 04 May 2015 11:11:25 GMT

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.

Re: How can I implement rank function in Proc sql?

user24feb — Mon, 04 May 2015 12:07:53 GMT

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;

Re: How can I implement rank function in Proc sql?

Kurt_Bremser — Mon, 04 May 2015 12:15:43 GMT

And in a data step:

data want;

set have;

by nr;

if first.nr

then rank = 1;

else rank + 1;

run;

Re: How can I implement rank function in Proc sql?

jakarman — Mon, 04 May 2015 13:00:14 GMT

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.

Re: How can I implement rank function in Proc sql?

Steelers_In_DC — Mon, 04 May 2015 13:02:47 GMT

data want;

set have;

by groupno;

rank +1;

if first.groupno then rank = 1;

run;

Re: How can I implement rank function in Proc sql?

AlokBishnoi — Wed, 19 Aug 2015 05:47:37 GMT

DATA D0;

INPUT GroupNo;

DATALINES;

;

/* 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;

Re: How can I implement rank function in Proc sql?

adi121 — Wed, 12 Jun 2019 03:10:31 GMT

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??