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

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
Users 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

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