In reply to Chris Hemedinger.

Hi Chris,

Thanks for this, the Query Builder is a very good task, I like it a lot and often use it to write queries for me, especially when joining multiple tables at the same time!

However, I have often wondered (and been asked) why you must GROUP BY at least one column and have a summarised column in the selection in the Query Builder before you are able to specify a HAVING filter. This implies a HAVING clause requires a GROUP BY clause and to output a summarised column which is not the case in SQL. For example it is difficult to re-create the following query in the Query Builder to select the tallest person from SASHELP.CLASS:

proc sql;
select *
from sashelp.class
having height = max(height);
quit;

As no GROUP BY clause is present and no summarised column is present on the SELECT clause the Query Builder does not let you specify a "Filter the Summarized Data" filter. Conversely I can easily pull out the tallest person of each (for example) sex using the Query Builder, although even then I am forced to keep MAX_of_height as a column in the output even though I might not want to output it.

The best I have managed to do so far is to create a new computed column which just contains a constant and then to GROUP BY that. This is a bit of a fudge and you have to keep the new computed column in the output too (removing it from the selected variables turns the GROUP BY clause into "GROUP BY 1" which is grouping by the first selected column rather than the constant "1". So the underlying SQL generated by the query builder is:

proc sql;
select *, max(height) as MAX_of_height,
1 as const /* Do not really want the last two columns in the output */
from sashelp.class
group by const
having height = max(height);
quit;

Is there a particular reason why the Query Builder requires a GROUP BY clause and a summarised column on the SELECT clause before a HAVING clause can be added?

Cheers,
Mark