About SQL

[Jianan_luna]

When I was analyzing table empT using SQL, the table is as following:

I want to find the minimum salary of managers, so I wrote the code as following:

proc sql;
select min(SAL) as min
from cert.emptt
where job='MANAGER';
quit;

I got my answer 2450, but I also want to know the manager's name, thus I wrote code as following:

proc sql;
select min(SAL) as min, ename
from cert.emptt
where job='MANAGER';
quit;

the I got the results:

It shows all manager's name with minimum salary, can you please let me know how can I get the answer of minimum salary with manager's name? Thanks so much

[Kurt_Bremser · Accepted solution]

Use a HAVING clause:

proc sql;
select min(SAL) as min, ename
from cert.emptt
where job='MANAGER'
having calculated min = sal
;
quit;

[Jianan_luna]

Thanks so much Sir, I got the right answer, but could you please explain why should I use having clause here? I am still confused

[Kurt_Bremser]

Maxim 3: Read the Log.

With your original code, you'll find a NOTE about remerging, so we need to prevent that.

To subset based on the result of a SQL summary function, you need HAVING, you cannot use it in a WHERE.

[Kurt_Bremser]

You can also do

proc sort
  data=cert.emptt (where=(job = "MANAGER"))
  out=want (obs=1)
;
by sal;
run;