Help using Base SAS procedures

How can I implement rank function in Proc sql?

Reply
Contributor ven
Contributor
Posts: 37

How can I implement rank function in Proc sql?

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.

Super User
Posts: 6,936

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 3,208

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

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.

---->-- ja karman --<-----
Super Contributor
Posts: 336

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

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;

Super User
Posts: 6,936

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

And in a data step:

data want;

set have;

by nr;

if first.nr

then rank = 1;

else rank + 1;

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 3,208

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

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.

---->-- ja karman --<-----
Valued Guide
Posts: 858

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

data want;

set have;

by groupno;

rank +1;

if first.groupno then rank = 1;

run;

N/A
Posts: 1

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

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;

Ask a Question
Discussion stats
  • 7 replies
  • 2701 views
  • 0 likes
  • 6 in conversation