DATA Step, Macro, Functions and more

Help in Rank function -Proc SQL

Accepted Solution Solved
Reply
Contributor
Posts: 70
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
‎07-26-2017 01:37 PM
PROC Star
Posts: 326

Re: Help in Rank function -Proc SQL

[ Edited ]
Posted in reply to Kalai2008

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
‎07-26-2017 01:37 PM
PROC Star
Posts: 326

Re: Help in Rank function -Proc SQL

[ Edited ]
Posted in reply to Kalai2008

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: 19,869

Re: Help in Rank function -Proc SQL

PROC RANK will do this as well.

Contributor
Posts: 70

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,492

Re: Help in Rank function -Proc SQL

Posted in reply to Kalai2008

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
  • 329 views
  • 3 likes
  • 4 in conversation