topic Re: PROC RANK order by descending in SAS Programming

PROC RANK order by descending

Zooyeon — Tue, 27 Apr 2021 07:57:39 GMT

I have a question about using PROC RANK function.

I usually use SQL in the company however I need to edit SAS query as well.

Problem :

I want to express below SQL query in SAS

RANK() OVER (PARTITION BY GROUP_NAME ORDER BY START_DATE DESC) AS SEQ

I tried below query but I need to make a rank based on START_DATE order by descending.

If anybody can give the answer of it, please let me know.

PROC RANK
DATA = WORK.DATA_EXTRACTION1
OUT = WORK.DATA_EXTRACTION2;
BY GROUP_NAME;
VAR START_DATE;
RANKS SEQ;
RUN;

Re: PROC RANK order by descending

ChrisNZ — Tue, 27 Apr 2021 08:36:49 GMT

Sadly window functions are not supported yet in SAS.

Have you tried the descending option for proc rank?

Re: PROC RANK order by descending

Zooyeon — Tue, 27 Apr 2021 11:33:20 GMT

Thanks for your quick reply 🙂
Then where should I put descending option in the SAS quary that I posted?
I tried put it anywhere I can put it, but It doesn't work well.

Re: PROC RANK order by descending

Kurt_Bremser — Tue, 27 Apr 2021 12:26:31 GMT

You will have to add a sort step to get the required order, as the DESCENDING option only controls how the ranks are assigned, but not how the output is sorted.

proc sort
  data=sashelp.class
  out=class
;
by sex;
run;

proc rank
  data=class 
  out=want
  ties=low
;
by sex;
var age;
ranks age_rank;
run;

proc sort data=want;
by sex descending age_rank;
run;

Re: PROC RANK order by descending

ChrisNZ — Tue, 27 Apr 2021 21:00:29 GMT

> It doesn't work well.

Please describe the issue; "does not work" is meaningless when looking for help. This option works as described.