 |
|
| Retrieving Data from a Single Table |
Selecting Columns in a Table
When you retrieve data from a table, you can select one or more columns by using variations of the basic SELECT statement.
| Selecting All Columns in a Table |
Use an asterisk in the SELECT clause to select all columns in a table. The following example selects all columns in the SQL.USCITYCOORDS table, which contains latitude and longitude values for U.S. cities:
proc sql outobs=12; title 'U.S. Cities with Their States and Coordinates'; select * from sql.uscitycoords;
Note: The OUTOBS= option limits the
number of rows (observations) in the output. OUTOBS= is similar to the OBS=
data set option. OUTOBS= is used throughout this document to limit the number
of rows that are displayed in examples. ![[cautionend]](../../../../common/61991/HTML/default/images/cautend.gif)
Note: In the tables
used in these examples, latitude values that are south of the Equator are
negative. Longitude values that are west of the Prime Meridian are also negative.
Selecting All Columns in a Table
U.S. Cities with Their States and Coordinates
City State Latitude Longitude
----------------------------------------------
Albany NY 43 -74
Albuquerque NM 36 -106
Amarillo TX 35 -102
Anchorage AK 61 -150
Annapolis MD 39 -77
Atlanta GA 34 -84
Augusta ME 44 -70
Austin TX 30 -98
Baker OR 45 -118
Baltimore MD 39 -76
Bangor ME 45 -69
Baton Rouge LA 31 -91
Note: When you select all columns, PROC SQL displays the columns
in the order in which they are stored in the table.
| Selecting Specific Columns in a Table |
To select a specific column in a table, list the name of the column in the SELECT clause. The following example selects only the City column in the SQL.USCITYCOORDS table:
proc sql outobs=12; title 'Names of U.S. Cities'; select City from sql.uscitycoords;
Names of U.S. Cities
City
------------------
Albany
Albuquerque
Amarillo
Anchorage
Annapolis
Atlanta
Augusta
Austin
Baker
Baltimore
Bangor
Baton Rouge
If you want to select more than one column, then you must separate the names of the columns with commas, as in this example, which selects the City and State columns in the SQL.USCITYCOORDS table:
proc sql outobs=12; title 'U.S. Cities and Their States'; select City, State from sql.uscitycoords;
U.S. Cities and Their States
City State
-------------------------
Albany NY
Albuquerque NM
Amarillo TX
Anchorage AK
Annapolis MD
Atlanta GA
Augusta ME
Austin TX
Baker OR
Baltimore MD
Bangor ME
Baton Rouge LA
Note: When you select specific columns, PROC SQL displays the columns
in the order in which you specify them in the SELECT clause.
| Eliminating Duplicate Rows from the Query Results |
In some cases, you might want to find only the unique values in a column. For example, if you want to find the unique continents in which U.S. states are located, then you might begin by constructing the following query:
proc sql outobs=12; title 'Continents of the United States'; select Continent from sql.unitedstates;
Selecting a Column with Duplicate Values
Continents of the United States
Continent
-----------------------------------
North America
North America
North America
North America
North America
North America
North America
North America
North America
North America
North America
Oceania
You can eliminate the duplicate rows from the results by using the DISTINCT keyword in the SELECT clause. Compare the previous example with the following query, which uses the DISTINCT keyword to produce a single row of output for each continent that is in the SQL.UNITEDSTATES table:
proc sql; title 'Continents of the United States'; select distinct Continent from sql.unitedstates;
Continents of the United States
Continent
-----------------------------------
North America
Oceania
Note: When you specify all of a table's columns in a SELECT clause
with the DISTINCT keyword, PROC SQL eliminates duplicate rows, or rows in
which the values in all of the columns match, from the results.
| Determining the Structure of a Table |
To obtain a list of all of the columns in a table and their attributes, you can use the DESCRIBE TABLE statement. The following example generates a description of the SQL.UNITEDSTATES table. PROC SQL writes the description to the log.
proc sql; describe table sql.unitedstates;
Determining the Structure of a Table (Partial Log)
NOTE: SQL table SQL.UNITEDSTATES was created like: create table SQL.UNITEDSTATES( bufsize=12288 ) ( Name char(35) format=$35. informat=$35. label='Name', Capital char(35) format=$35. informat=$35. label='Capital', Population num format=BEST8. informat=BEST8. label='Population', Area num format=BEST8. informat=BEST8., Continent char(35) format=$35. informat=$35. label='Continent', Statehood num );
|
|
Copyright © 2008 by SAS Institute Inc., Cary, NC, USA. All rights reserved.