topic Re: Help in Rank function -Proc SQL in SAS Programming

Help in Rank function -Proc SQL

Kalai2008 — Wed, 26 Jul 2017 17:16:51 GMT

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

Re: Help in Rank function -Proc SQL

kiranv_ — Wed, 26 Jul 2017 17:30:09 GMT

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

Re: Help in Rank function -Proc SQL

Reeza — Wed, 26 Jul 2017 17:32:38 GMT

PROC RANK will do this as well.

Re: Help in Rank function -Proc SQL

Kalai2008 — Wed, 26 Jul 2017 17:37:16 GMT

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

Re: Help in Rank function -Proc SQL

art297 — Wed, 26 Jul 2017 17:45:51 GMT

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