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

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