I need to find top 5 Transaction_Due_Date in Proc sql

Question

This code works for top value but I need top 5 values

proc sql;                                                                                                                                                      
create table cash.gO5 as                                                                                                                                         

select * , max(Transaction_Due_Date) as max1 format = date9.                                                                                                     
from cash.Orders_Dim65                                                                                                                                           
group by Customer_Name;                                                                                                                                          

quit;  

Answer 1

PROC SQL does not support order analytical functions such as rank() as found in other flavors of SQL; however, there are numerous ways in which you can get a rank by group. Here are a few options you can use.

Option 1: PROC RANK

proc rank does exactly what it sounds like: ranks stuff. Note that your data must be sorted if being used in SAS 9 or SPRE.

proc rank data=sashelp.cars
          out=want(where=(msrp_rank LE 5))
          descending;
    by make;

    var msrp;           /* Variable to rank */
    ranks msrp_rank;    /* Name of variable holding ranks */
run;

Option 2: Data Step

You can rank using a data step. Note that your data must be sorted if using SAS 9 or SPRE.

proc sort data=sashelp.cars
          out=cars;
    by make descending msrp;
run;

data want;
    set cars;
    by make descending msrp;

    if(first.make) then Rank = 0;

    Rank+1;

    if(Rank LE 5);
run;

Option 3: simple.topK CAS Action

If you have Viya, you can use CAS actions to quickly rank large datasets. This can be used in both SAS and Python with the SWAT package.

/* Load sashelp.cars into CAS */
data casuser.cars;
    set sashelp.cars;
run;

proc cas;
    simple.topk result=r /
        table      = {caslib='casuser' name='cars' groupby='make'}
        casout     = {caslib='casuser' name='cars_top_5' replace=true}
        aggregator ='max'
        bottomK    = 0
        topK       = 5
        inputs     = {{name='msrp'}}
    ;
quit;