topic Re: About SQL in New SAS User

About SQL

Jianan_luna — Thu, 17 Sep 2020 19:10:59 GMT

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

Re: About SQL

Kurt_Bremser — Thu, 17 Sep 2020 19:14:14 GMT

Use a HAVING clause:

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

Re: About SQL

Jianan_luna — Thu, 17 Sep 2020 19:18:08 GMT

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

Re: About SQL

Kurt_Bremser — Thu, 17 Sep 2020 19:18:12 GMT

You can also do

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

Re: About SQL

Kurt_Bremser — Thu, 17 Sep 2020 19:22:54 GMT

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.