BookmarkSubscribeRSS Feed
Zooyeon
Calcite | Level 5

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;  

 

4 REPLIES 4
ChrisNZ
Tourmaline | Level 20

Sadly window functions are not supported yet in SAS.

Have you tried the descending option for proc rank?

Zooyeon
Calcite | Level 5
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.
ChrisNZ
Tourmaline | Level 20

> It doesn't work well.

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

 

Kurt_Bremser
Super User

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1877 views
  • 3 likes
  • 3 in conversation