O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

libname sql 'SAS-library';
proc sql;
title 'Total Populations of Continents with More than 15 Countries';
select Continent,
sum(Population) as TotalPopulation format=comma16.,
count(*) as Count
from sql.countries
group by Continent
having count(*) gt 15
order by Continent;
The HAVING expression contains the COUNT function, which counts the number of
rows within each group.
Output 2.48 Using HAVING with the COUNT Function
Validating a Query
The VALIDATE statement enables you to check the syntax of a query for correctness
without submitting it to PROC SQL. PROC SQL displays a message in the log to
indicate whether the syntax is correct.
libname sql 'SAS-library';
proc sql;
validate
select Name, Statehood
from sql.unitedstates
where Statehood lt '01Jan1800'd;
Log 2.3 Validating a Query (Partial Log)
3 proc sql;
4 validate
5 select Name, Statehood
6 from sql.unitedstates
7 where Statehood lt '01Jan1800'd;
NOTE: PROC SQL statement has valid syntax.
The following example shows an invalid query and the corresponding log message:
Validating a Query 71
libname sql 'SAS-library';
proc sql;
validate
select Name, Statehood
from sql.unitedstates
where lt '01Jan1800'd;
Log 2.4 Validating an Invalid Query (Partial Log)
3 proc sql;
4 validate
5 select Name, Statehood
6 from sql.unitedstates
7 where lt '01Jan1800'd;
------------
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **,
+, -, /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, GE,
GROUP,
GT, HAVING, LE, LIKE, LT, NE, OR, ORDER, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The SAS System stopped processing this step because of errors.
72 Chapter 2 Retrieving Data from a Single Table

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required