topic Re: When I remove the title in the subquery the query runs fine with 2 columns please check in SAS Programming

When I remove the title in the subquery the query runs fine with 2 columns please check

hmlong25 — Mon, 10 Feb 2025 18:49:58 GMT

proc sql;

select avg(PopEstimate1), "Average Estimated Population"

from sq.statepopulation;

quit;

proc sql;

select Name, PopEstimate1

from sq.statepopulation

where PopEstimate1 > (select avg(PopEstimate1),"Average Estimated Population"

from sq.statepopulation);

quit;

Re: When I remove the title in the subquery the query runs fine with 2 columns please check

A_Kh — Mon, 10 Feb 2025 19:20:16 GMT

In your first code comma (,) separates the variable and it's label which is incorrect. Comma separates only variables. So remove the comma and see if this works.

proc sql;
select avg(PopEstimate1)  "Average Estimated Population"
from sq.statepopulation;
quit;

Re: When I remove the title in the subquery the query runs fine with 2 columns please check

Tom — Mon, 10 Feb 2025 19:29:00 GMT

What are you trying to do?

Your first query returns two variables (what you called columns), neither of which has been given a NAME. Let's change it to use SASHELP.CLASS so we can experiment easily.

proc sql;
select avg(age), "Average Estimated Age"
from sashelp.class;
quit;

Result

If you want to give that variable a LABEL then remove the comma.

proc sql;
select avg(age) "Average Estimated Age"
from sashelp.class;
quit;

Result

You might also want to give the variable a NAME.

proc sql;
select avg(age) as avg_age "Average Estimated Age"
from sashelp.class;
quit;

To be clearer that the quoted string is just the LABEL for the variable add the LABEL= keyword.

proc sql;
select avg(age) as avg_age label="Average Estimated Age"
from sashelp.class;
quit;

For your second query you need to return only ONE value in the subquery to be able to use it with the > comparison operator. So again you need to remove the comma. You could leave the label, but why? There is no way for that label to be printed or saved anywhere with the way that query is constructed.

proc sql;
select Name, age
  from sashelp.class
  where age > (select avg(age) from sashelp.class)
;
quit;

You can avoid the subquery (at least in PROC SQL) if you use the HAVING clause instead of the WHERE clause.

proc sql;
select Name, age
  from sashelp.class
  having age > avg(age)
;
quit;

Because as the SAS log states PROC SQL will remerge the aggregate function results for you.

504  proc sql;
505  select Name, age
506    from sashelp.class
507    having age > avg(age)
508  ;
NOTE: The query requires remerging summary statistics back with the original
      data.
509  quit;

Re: When I remove the title in the subquery the query runs fine with 2 columns please check

hmlong25 — Mon, 10 Feb 2025 21:48:23 GMT

The comma put the Label next to the average amount instead of above it. I should not have the comma, I agree. I think the Label= option is a better one also

Re: When I remove the title in the subquery the query runs fine with 2 columns please check

hmlong25 — Mon, 10 Feb 2025 21:54:14 GMT

Thank you! Is it easier to use the Having clause when the query requires remerging summary stats back with the original instead of using a subquery as a general rule?

Re: When I remove the title in the subquery the query runs fine with 2 columns please check

Tom — Mon, 10 Feb 2025 21:57:28 GMT

@hmlong25 wrote:
Thank you! Is it easier to use the Having clause when the query requires remerging summary stats back with the original instead of using a subquery as a general rule?

That is a matter personal preferance.

I find it easier and clearer.

But if you have to use other SQL dialects you might not want to use it since they generally not support it so you might want to use a method that works the same in all of the SQL dialects you have to use commonly.