05-04-2015 12:45 AM
How can I implement rank function in SAS Proc sql which will give below output?
I need to use it in SAS DI without using custom coding, which will happen if I didn't use proc sql( or let me know if it can be done in another way in SAS DI by using metadata only). Yet there is no simple proc sql implementation guess will have to use the data step approach. Thanks all.
05-04-2015 06:57 AM
The SAS RANK function (SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition) returns the position of a character in the collating sequence.
I guess you meant something else? Please clarify your intentions.
05-04-2015 07:11 AM
The rank function of oracle/ms Ranking Functions (Transact-SQL) does not exist in SAS (ansi-sql 99). It is the 2013 ansi version Select (SQL) - Wikipedia, the free encyclopedia) that got introduced
You example is showing a grouped in a window. As SAS is an analytical system those analytics have been solves al long time before that by the statistical procs supporting by/class statements.
Call those "sas procs" pacakages for modernizing the words not the meanings.
05-04-2015 08:07 AM
I believe you could mean this (count by group) ;-)
Input Nr @@;
1 1 1 2 2 3 3 3 4 4 4 4 5 5 5
Create Table Want As
Select a.Nr, Monotonic()-b.Min_Grp+1 As Rank From Have a
Left Join (Select Nr, Min(Monotonic()) As Min_Grp From Have Group By Nr) b
On a.Nr eq b.Nr;
05-04-2015 08:15 AM
And in a data step:
then rank = 1;
else rank + 1;
05-04-2015 09:00 AM
Would avoid the monotonic function. It is an undocumented feature and most likey not reliable with the multi-threading capabilities
Processing the data sequential (data-step) is the most easy one and will perform well even with bigger datasets.
Using the reporting and statistical procs is a better approach when the logical question is the appropriate fit.
Recoding Excel SQL or Cobol into SAS is just a bad idea.
08-19-2015 01:47 AM
/* You can skip sorting if data is already sorted */
proc sort data=d0; by GroupNo; run;
/* You can put any other formated variable than A but keep in mind that a formated value should not be equal to actual value of the variable in any of the observation */
create table d0_fin(DROP=A) as
select PUT(GroupNo,Z5.) AS A, GroupNo,
(monotonic() - min(monotonic())+1) as Rank
group by a
order by a, monotonic();