topic Re: dense_rank over (partitioning in SAS Procedures

dense_rank over (partitioning

SunilJ — Tue, 28 Jul 2015 12:59:36 GMT

Hi,

Following is oracle query and I am trying to do it in SAS. I did not find any alternative. Can someone please help me in this, I really appreciate.

SELECT APPS.*,

DENSE_RANK() OVER (PARTITION BY APPS.APP_NBR ORDER BY APPS.V_RANK_DAYS2APP, ABS(V_RANK_DAYS2APP),

DECODE(MATCHED_ON,7,1, 5,2, 3,3, 6,4, 1,5, 4,6, 2,7, 9), ROWNUM) AS V_DAYS2APP_RANK

FROM APPS

Thanks,

Sunny

Re: dense_rank over (partitioning

Patrick — Tue, 28 Jul 2015 13:11:59 GMT

PROC RANK would allow you to do ranking using "dense". I've never used "decode()" and just looked it up in the Oracle documentation. From what I understand this would require some post processing of the result returned by PROC RANK (so an additional PROC SQL/Data Step on the output of the PROC RANK procedure via an additional pass through the data).