DATA Step, Macro, Functions and more

proc sql equal to rank function in SQL

Reply
Occasional Contributor
Posts: 7

proc sql equal to rank function in SQL

The RANK function in SQL is often used to dedup a dataset by specifying the first observation to keep. RANK can also be used to number records within a group. There is no clear substitute for RANK in SAS, i don't want a data step. i want a proc sql.

SELECT a.*, RANK() over (partition by cust_id order by date_created) as n
FROM inputds a
Order by cust_id, date_created

====================================================

 

i want the sas proc sql equivalent to this.

 

Thanks

Gmorla

 

 

Super User
Posts: 17,801

Re: proc sql equal to rank function in SQL


gmorla2016 wrote:

 i don't want a data step. i want a proc sql.

?..

 

i want the sas proc sql equivalent to this.

 

Thanks

Gmorla

 

 


 

Why? 

 

Look at the monotonic() function which is unsupported, or use SQL pass through. 

Super User
Posts: 9,676

Re: proc sql equal to rank function in SQL

If you already have an index variable N . You can do this  like:

 

 

data class;
 set sashelp.class;
 n+1;
run;


proc sql;
 select (select count(*) from class where sex=a.sex and n<=a.n) as rank,a.*
  from class as a
   order by sex,n;
quit;
Ask a Question
Discussion stats
  • 2 replies
  • 1201 views
  • 0 likes
  • 3 in conversation