07-30-2017 02:29 PM
Hi All, can you please explain crisply how the below query is working,
i wanted to know how where confition checking the each observtion of table b with every observation of table a.
/* Quesry is used to find the ranks*/
input id sal;
select a.*,(select count(distinct sal) from a where b.sal le a.sal) as r from a b;
07-30-2017 09:38 PM
Conceptually, SAS is running a subquery for every observation of table a. Since that subquery also involves table a, you must give the table an alias name (b) so as to allow an expression like "b.sal le a.sal". I think the query should be written as
proc sql; select b.*, ( select count(distinct id) from a where b.sal le a.sal ) as r from a as b; quit;
07-31-2017 10:48 AM
Hi, I wanted to know the reason why b.sal le a.sal
comparing 1 observation of B table to every observation of A table .. ?
This would one approach to "how many salary values are the same or more than the person with the givend ID, including that person".
Is a "what" answer. Why is up to your rules.