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īut SAS SQL still has not gotten around to that. The MONOTONIC() function is still (I think) not documented in SAS SQL, but it will probably not change.
We can develop Reuseble applications this applications we can use unlimited times. What is the purpose of SAS Macro ANS: SAS macros stores in auto call library. It is developed to avoid the calling of that function again and again. What is SAS Macro ANS: SAS Macro is a user defined function. 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. SAS Macro Interview Question and Answers. the difference being the ORDER BY clause in the inner query. On the other hand, if you wanted the RANK variable to mirror the ranking of SAL within NAME, it is possible: proc sql
Your query is by definition indeterministic, meaning that you are not guaranteed to get the same results every time. 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. 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 īut 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. 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. What you want is, by definition, not really doable with SQL.
0 kommentar(er)
