How can I implement rank function in SAS Proc sql which will give below output?
GroupNo Rank
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
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.
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.
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.
I believe you could mean this (count by group) 😉
Data Have;
Input Nr @@;
Datalines;
1 1 1 2 2 3 3 3 4 4 4 4 5 5 5
;
Run;
Proc SQL;
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;
Quit;
And in a data step:
data want;
set have;
by nr;
if first.nr
then rank = 1;
else rank + 1;
run;
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.
data want;
set have;
by groupno;
rank +1;
if first.groupno then rank = 1;
run;
DATA D0;
INPUT GroupNo;
DATALINES;
1
1
1
2
2
3
3
3
;
/* 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 */
proc sql;
create table d0_fin(DROP=A) as
select PUT(GroupNo,Z5.) AS A, GroupNo,
(monotonic() - min(monotonic())+1) as Rank
from d0
group by a
order by a, monotonic();
quit;
Can anyone tell me how (monotonic() - min(monotonic())+1) this is working and assigning the rank as 1,2,..so on .
Monotonic will give 1,2,3, and so on and min(monotonic) is 1 so i dont get how it is calculating the rank on which basis??
1-1+1=1
2-1+1=2
is this the way it is working??
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.