topic Re: proc sql equal to rank function in SQL in SAS Programming

proc sql equal to rank function in SQL

gmorla2016 — Mon, 16 May 2016 15:22:07 GMT

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

Re: proc sql equal to rank function in SQL

Reeza — Mon, 16 May 2016 16:03:10 GMT

@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.

Re: proc sql equal to rank function in SQL

Ksharp — Tue, 17 May 2016 07:56:19 GMT

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;