Selecting Columns in a Table

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 USCityCoords table, which contains latitude and longitude values for U.S. cities:

proc sql outobs=12;
   title1 'U.S. Cities with Their States and Coordinates';
   title2 'First 12 Rows Only';
   select * 
      from uscitycoords;
quit;

Selecting All Columns in a 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 USCityCoords table:

proc sql outobs=12;
   title1 'Names of U.S. Cities';
   title2 'First 12 Rows Only';
   select City
      from uscitycoords;
quit;

Selecting One Column

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 USCityCoords table:

proc sql outobs=12;
   title1 'U.S. Cities and Their States';
   title2 'First 12 Rows Only';
   select City, State
      from uscitycoords;
quit;

Selecting Multiple Columns

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 on table UnitedStates:

proc sql outobs=12;
   title1 'Continents of the United States';
   title2 'First 12 Rows Only';
   select Continent 
      from unitedstates;
quit;

Selecting a Column with Duplicate Values

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 UnitedStates table:

proc sql;
   title 'Continents of the United States';
   select distinct Continent 
      from unitedstates;
quit;

Eliminating Duplicate Values

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 UnitedStates table. PROC SQL writes the description to the log.

proc sql;
   describe table unitedstates;
quit;

NOTE: SQL table UNITEDSTATES was created like:

create table UNITEDSTATES( bufsize=65536 )
  (
   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
  );