What you want is, by definition, not really doable with SQL. What you want is a variable named RANK which is just the arbitrary order of data within your table, not the order as defined by some other variables. It is possible to get the results you want in SQL in most SAS editions, provided that your data is created like your example dataset: proc sql;
select name,sal,r-min(r)+1 as rank from(
select name,sal,monotonic() as r
from a)
group by name
order by 1,3
; But this depends on using the order of the data in the input table - by definition, a SQL table is not ordered and does not have to be delivered to a query in physical order when using SQL. This means that if you do the above, you may get the result you want when doing the query against a simple SAS table; if you use another type of libname, be it MS-SQL, Oracle, Hadoop or some SAS server, there is no guarantee that you will get the data in that order. Your query is by definition indeterministic, meaning that you are not guaranteed to get the same results every time. On the other hand, if you wanted the RANK variable to mirror the ranking of SAL within NAME, it is possible: proc sql;
select name,sal,r-min(r)+1 as rank from(
select name,sal,monotonic() as r
from a order by name,sal)
group by name
order by 1,3
; - the difference being the ORDER BY clause in the inner query. But this means that the lowest SAL for a NAME would get RANK=1, etc (use DESC if you want the highest SAL having RANK=1), not the data you showed us as you desired output. The MONOTONIC() function is still (I think) not documented in SAS SQL, but it will probably not change. In other SQL dialects, such as Oracle or MS, you would use a RANK summary function or similar to get the rank, something like: select name,sal,rank over (partition by name order by sal) as rank
order by 1,3
; but SAS SQL still has not gotten around to that. And note that you will have to have an ORDER BY in the definition of RANK, as SQL tables are by definition not ordered beforehand. To be sure that you get what you want, you will have to use a SAS datastep: Data b;
do rank=1 by 1 until(last.name);
set a; by name;
output;
end;
run;
proc sql;
select name,sal,rank from b;
quit; Although I would advise against using the form at all, you can get the records with RANK=2 from the first, indeterministic(!) query by using a HAVING clause: proc sql;
select name,sal,r-min(r)+1 as rank from(
select name,sal,monotonic() as r
from a)
group by name
having rank=2
order by 1
;
... View more