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

Output 1.2 Sample DATA Step Output

This example shows that PROC SQL can achieve the same results as Base SAS software

but often with fewer and shorter statements. The SELECT statement that is shown in this

example performs summation, grouping, sorting, and row selection. It also displays the

query's results without the PRINT procedure.

PROC SQL executes without using the RUN statement. After you invoke PROC SQL

you can submit additional SQL procedure statements without submitting the PROC

statement again. Use the QUIT statement to terminate the procedure.

Notes about the Example Tables

For all examples, the following global statement is in effect:

libname sql 'SAS-library';

The tables that are used in the whole section of “Using the SQL Procedure” contain

geographic and demographic data. The data is intended to be used for the PROC SQL

code examples only. The data is not necessarily up-to-date or accurate.

These tables are available in a ZIP file at http://support.sas.com/documentation/

onlinedoc/base/index.html. Look for SAS SQL Procedure User’s Guide on this page.

Download and extract the ZIP file to a location that is accessible by SAS. The extracted

ZIP file is a transport file that contains the tables.

Start SAS. Submit this code to import the tables:

/* Substitute the pathname of your Sasuser

directory for 'your-Sasuser-directory-path'

and the appropriate pathname and filename

for 'your-downloaded-file-location' */

libname new 'your-Sasuser-directory-path';

filename trans 'your-downloaded-file-location';

Notes about the Example Tables 7

proc cimport library=new infile=trans;

run;

/* Assign a libref named SQL to provide access to some

of the sample data sets. */

libname sql 'your-Sasuser-directory-path';

The tables that follow show partial output for these data sets.

You can find all of the example SQL programs that are shown in the “Using the SQL

Procedure” section in Appendix 3, “Example Code Shown in Using the SQL

Procedure,” on page 393. If you copy the code to a SAS editor, you can preserve the

code spacing by copying the HTML version of the code.

The Countries table contains data that pertains to countries. The Area column contains a

country's area in square miles. The UNDate column contains the year in which a country

entered the United Nations, if applicable.

Output 1.3 Countries (Partial Output)

The WorldCityCoords table contains latitude and longitude data for world cities. Cities

in the Western hemisphere have negative longitude coordinates. Cities in the Southern

8 Chapter 1 • Introduction to the SQL Procedure

hemisphere have negative latitude coordinates. Coordinates are rounded to the nearest

degree.

Output 1.4 WorldCityCoords (Partial Output)

Notes about the Example Tables 9

The USCityCoords table contains the coordinates for cities in the United States. Because

all cities in this table are in the Western hemisphere, all of the longitude coordinates are

negative. Coordinates are rounded to the nearest degree.

Output 1.5 USCityCoords (Partial Output)

10 Chapter 1 • Introduction to the SQL Procedure

The United States table contains data that is associated with the states. The Statehood

column contains the date on which the state was admitted into the Union.

Output 1.6 United States (Partial Output)

Notes about the Example Tables 11