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;
/* 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 391. 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
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