Help in Rank function -Proc SQL

[Kalai2008]

I would like to use the Rank function in Proc sql

Getting error, when I use this code

RANK() OVER(ORDER BY t.ID DESC) as rank,

Output like this,

ID New col
10 1
10 2
10 3
23 1
25 1
45 1
50 1
50 2
65 1

[kiranv_ · Accepted solution]

Rank over and others are known as Order analytical functions and  are not available in Proc SQL. But you can use proc sort and use first.variable concept to give rank

data have;
input id;
datalines;
10 
10 
10 
23 
25 
45 
50 
50 
65 
;

proc sort data =  have;
by id;
run;

data want;
set have;
by id;
if first.id then rank =1;
else rank+1;
run;

and then use first.ID to get the desired result in sas datastep

[Reeza]

PROC RANK will do this as well.

[Kalai2008]

Thank you! I already did this in SAS, but trying to do in Proc SQL.

[art297]

The following will work using proc sql:

proc sql;
  create table want as
  select *,
   (select count(distinct b.weight)
     from sashelp.class b
       where b.weight <= a.weight
         and a.sex eq b.sex) as rank
           from sashelp.class a
  ;
quit;

Art, CEO, AnalystFinder.com