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

Queries

Queries retrieve data from a table, view, or DBMS. A query returns a query result, which

consists of rows and columns from a table. With PROC SQL, you use a SELECT

statement and its subordinate clauses to form a query. Chapter 2, “Retrieving Data from

a Single Table,” on page 19 describes how to build a query.

Views

PROC SQL views do not actually contain data as tables do. Rather, a PROC SQL view

contains a stored SELECT statement or query. The query executes when you use the

view in a SAS procedure or DATA step. When a view executes, it displays data that is

derived from existing tables, from other views, or from SAS/ACCESS views. Other SAS

procedures and the DATA step can use a PROC SQL view as they would any SAS data

file. For more information about views, see Chapter 4, “Creating and Updating Tables

and Views,” on page 111.

Note: When you process PROC SQL views between a client and a server, getting the

correct results depends on the compatibility between the client and server

architecture. For more information, see “Access a SAS View” in SAS/CONNECT

User’s Guide.

Null Values

According to the ANSI standard for SQL, a missing value is called a null value. It is not

the same as a blank or zero value. However, to be compatible with the rest of SAS,

PROC SQL treats missing values the same as blanks or zero values, and considers all

three to be null values. This important concept comes up in several places in this

document.

Comparing PROC SQL with the SAS DATA Step

PROC SQL can perform some of the operations that are provided by the DATA step and

the PRINT, SORT, and SUMMARY procedures. The following query displays the total

population of all the large countries (countries with population greater than 1 million) on

each continent.

proc sql;

title 'Population of Large Countries Grouped by Continent';

select Continent, sum(Population) as TotPop format=comma15.

from sql.countries

where Population gt 1000000

group by Continent

order by TotPop;

quit;

Comparing PROC SQL with the SAS DATA Step 5

Output 1.1 Sample SQL Output

Here is a SAS program that produces the same result.

title 'Large Countries Grouped by Continent';

proc summary data=sql.countries;

where Population > 1000000;

class Continent;

var Population;

output out=sumPop sum=TotPop;

run;

proc sort data=SumPop;

by totPop;

run;

proc print data=SumPop noobs;

var Continent TotPop;

format TotPop comma15.;

where _type_=1;

run;

6 Chapter 1 • Introduction to the SQL Procedure