The SELECT statement retrieves and displays data. It consists of a SELECT clause and several optional clauses that can be used within the SELECT statement. Each clause begins with a keyword and is followed by one or more components. The optional clauses name the input data set, subset, group, or sort the data.

A PROC SQL step that contains one or more SELECT statements is referred to as a PROC SQL query. The SELECT statement is only one of several statements that can be used with PROC SQL.

When you construct a SELECT statement, you must specify the clauses in the following order:

To specify which columns to display in a query, write a SELECT clause. After the keyword SELECT, list one or more column names and separate the column names with commas. The SELECT clause specifies existing columns and can create columns. The existing columns are already stored in a table.

The following SELECT clause specifies the columns EmpID, JobCode, Salary, and bonus. The columns EmpID, JobCode, and Salary are existing columns. The column named Bonus is a new column. The column alias appears as a column heading in the output and matches the case that you used in the SELECT clause.

proc sql;
   select empid, jobcode, salary, salary*.06 as bonus
      from certadv.payrollmaster
      where salary<32000
      order by jobcode;
quit;

Use an asterisk (*) in the SELECT clause to display all columns in the order in which they are stored in a table. All rows are displayed, by default, unless you limit or subset them.

The following SELECT statement displays all columns and rows in the table Certadv.Staffchanges. Certadv.Staffchanges lists all employees in a company who have had changes in their employment status.

proc sql;
   select *
      from certadv.staffchanges;
quit;

When you specify SELECT * you can use the FEEDBACK option in the PROC SQL statement to write the expanded list of columns to the SAS log. For example, the PROC SQL query shown below contains the FEEDBACK option:

proc sql feedback;
   select *
      from certadv.staffchanges;
quit;

NOTE: Statement transforms to:

        select STAFFCHANGES.EmpID, STAFFCHANGES.LastName, STAFFCHANGES.FirstName,
STAFFCHANGES.City, STAFFCHANGES.State, STAFFCHANGES.PhoneNumber
          from CERTADV.STAFFCHANGES;

The FEEDBACK option is a debugging tool that lets you see exactly what is being submitted to the SQL processor. The resulting message in the SAS log expands asterisks (*) into column lists. It also resolves macro variables and places parentheses around expressions to show their order of evaluation.

You can create new columns that contain either text or a calculation. New columns appear in output, along with any existing columns that are selected. The new columns exist only for the duration of the query, unless a table or a view is created.

To create a new column, include any valid SAS expression in the SELECT clause list of columns. You can assign a column alias, a name, to a new column by using the keyword AS followed by the name that you would like to use.

proc sql;
   select empid, jobcode, salary, salary*.06 as bonus
      from certadv.payrollmaster
      where salary<32000
      order by jobcode;
quit;

A column alias is useful because it enables you to reference the column elsewhere in the query. The column alias appears as a column heading in the output and matches the case that you used in the SELECT clause. You can specify a label for an existing or a new column in the SELECT clause. If both a label and a column alias are specified for a new column, the label is displayed as the column heading in the output[1]. If only a column alias is specified, specify the column alias exactly as you want it to appear in the output.

You can use the DISTINCT keyword to eliminate duplicate rows. The DISTINCT keyword applies to all columns, and only those columns, that are listed in the SELECT clause.

proc sql;
   select distinct flightnumber, destination
      from certadv.internationalflights;
quit;