DATA Step, Macro, Functions and more

Help in Rank function -Proc SQL

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Help in Rank function -Proc SQL

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

 


Accepted Solutions
Solution
4 weeks ago
PROC Star
Posts: 252

Re: Help in Rank function -Proc SQL

[ Edited ]

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

 

View solution in original post


All Replies
Solution
4 weeks ago
PROC Star
Posts: 252

Re: Help in Rank function -Proc SQL

[ Edited ]

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

 

Super User
Posts: 17,831

Re: Help in Rank function -Proc SQL

PROC RANK will do this as well.

Contributor
Posts: 53

Re: Help in Rank function -Proc SQL

Thank you! I already did this in SAS, but trying to do in Proc SQL.
PROC Star
Posts: 7,363

Re: Help in Rank function -Proc SQL

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

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 114 views
  • 3 likes
  • 4 in conversation