Hi Team
Please help me with this scenario.I want to solve it by Proc Sql and not base SAS as i was able to solve it by first.variable and Proc rank so i need it to solve using Proc Sql.
Dataset:-
DATA SALARY1;
INPUT EmpName $ Salary Department $;
DATALINES;
Steve 150 Finance
James 120 IT
Andrew 120 Finance
Mark 100 Back
Adam 110 IT
Niti 140 IT
Aman 110 HR
Lewis 130 Finance
Smith 125 Back
;
RUN;
Output:-
Andrew 120 Finance
Adam 110 IT
Q1.If there is no third observartion for particular department i don`t need that department salary to show up.
Q2.If there is no third observation for particular department i need the last salary for that department.
Thanks in Advance.Please help.
Here is a solution for Q1, including a tie breaker
DATA Salary2;
INPUT EmpName $ Salary Department $;
DATALINES;
Steve 150 Finance
James 120 IT
Andrew 120 Finance
Mark 100 Back
Adam 110 IT
Niti 140 IT
Aman 110 HR
Lewis 130 Finance
Smith 125 Back
PG 140 IT <-- tied with Niti
;
proc sql;
/* Break ties */
create table S as
select *, salary + 0.0001*rand("uniform") as s
from Salary2;
create table thirdSalary as
select EmpName, Salary, Department
from
(select *
from
(select *
from S
group by Department
having s < max(s))
group by Department
having s < max(s))
group by department
having s = max(s);
drop table S;
quit;
If you don't need to break ties then replace dataset S with Salary2 and variable s with Salary.
Q2: There is no such thing as the last observation in SQL. You would need an observation number to solve this problem in SQL.
Thanks for the help PG Stats
Here is for Q1. DATA SALARY1; INPUT EmpName $ Salary Department $; DATALINES; Steve 150 Finance James 120 IT Andrew 120 Finance Mark 100 Back Adam 110 IT Niti 140 IT Aman 110 HR Lewis 130 Finance Smith 125 Back ; RUN; proc sql; select *,(select count(distinct salary) from salary1 where department=a.department and salary ge a.salary) as n from salary1 as a where calculated n=3; quit; If you want Q2, you need make an index variable like n+1, or you only want salary's max or min ?
Hi Ksharp
Thank's for the help can you please describe the logic a bit it will be helpful
Get each obs 's rank within each department . In other words, the max has rank(n) 1,the second has rank 2 ........ and pick up the obs has the rank 3 (the third largest) .
Hi KSharp
I already did it with help of Proc rank, but I wanted to understand the logic of your first solution. If possible can you please explain me the logic running behind the nested Query.
Thanks in advance.
Works perfectly and it is actually a very good Logic took time to understand
If you want to explore all the ways SAS can skew a cat, the best way to solve this question imho is to use proc summary and then a data a step to implement Q1 or Q2 or any other scenario. Compact and (somewhat) legible syntax, and no prior sorting required.
proc summary nway;
class DEPARTMENT;
var SALARY;
output out=STATS
max(SALARY)=SALARY
idgroup(max(SALARY)out[3](EMPNAME)=EMPNAME);
run;
Another version of @Ksharp's idea, maybe simpler because it doesn't involve a correlated subquery:
DATA Salary2;
INPUT EmpName $ Salary Department $;
DATALINES;
Steve 150 Finance
James 120 IT
Andrew 120 Finance
Mark 100 Back
Adam 110 IT
Niti 140 IT
Aman 110 HR
Lewis 130 Finance
Smith 125 Back
PG 140 IT <-- tied with Niti
;
proc sql;
/* Break ties */
create table S as
select *, salary + 0.0001*rand("uniform") as s
from Salary2;
create table thirdSalary as
select a.EmpName, a.Salary, a.Department
from
S as a left join
S as b on a.Department=b.Department
group by a.EmpName, a.Salary, a.Department
having sum(b.s>a.s) = 2;
drop tabel S;
quit;
Thanks every one for the help. I have got my answer and i have also gone through the details of using subquery.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.