BookmarkSubscribeRSS Feed
Nitish1003
Obsidian | Level 7

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.

 

10 REPLIES 10
PGStats
Opal | Level 21

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.

PG
Nitish1003
Obsidian | Level 7

Thanks for the help PG Stats

Ksharp
Super User
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 ?

Nitish1003
Obsidian | Level 7

Hi Ksharp

Thank's for the help can you please describe the logic a bit it will be helpful

Ksharp
Super User
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) .

Nitish1003
Obsidian | Level 7

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.

Raj03
Calcite | Level 5

Works perfectly and it is actually a very good Logic took time to understand 

ChrisNZ
Tourmaline | Level 20

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; 
PGStats
Opal | Level 21

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;
PG
Nitish1003
Obsidian | Level 7

Thanks every one for the help. I have got my answer and i have also gone through the details of using subquery.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 4102 views
  • 4 likes
  • 5 in conversation