Writing a PROC SQL Step

Overview

Before creating a query, you must first reference the library in which your table is stored. Then you write a PROC SQL step to query your table.
General form, basic PROC SQL step to perform a query:
PROC SQL;
SELECT column-1<,...column-n>
FROM table-1|view-1<,...table-n|view-n>
<WHERE expression>
<GROUP BY column-1<, ... column-n>>
<ORDER BY column-1<,... column-n>>;
Here is an explanation of the syntax:
PROC SQL
invokes the SQL procedure
SELECT
specifies the column(s) to be selected
FROM
specifies the table(s) to be queried
WHERE
subsets the data based on one or more conditions
GROUP BY
classifies the data into groups based on the specified column(s)
ORDER BY
sorts the rows that the query returns by the value(s) of the specified column(s).
CAUTION:
Unlike other SAS procedures the order of clauses with a SELECT statement in PROC SQL is important. Clauses must appear in the order shown above.
Note: A query can also include a HAVING clause, which is introduced at the end of this chapter. To learn more about the HAVING clause, see Performing Advanced Queries Using PROC SQL.

The SELECT Statement

The SELECT statement, which follows the PROC SQL statement, retrieves and displays data. It consists of clauses that begin with a keyword, and is followed by one or more components. The SELECT statement in the following sample code contains four clauses: the required clauses SELECT and FROM, and the optional clauses WHERE and ORDER BY. The end of the statement is indicated by a semicolon. 
proc sql;
 |-select empid,jobcode,salary,
 |        salary*.06 as bonus
 |----from sasuser.payrollmaster
 |----where salary<32000
 |----order by jobcode;
Note: 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.
The following PROC SQL query creates the output report that is shown: 
proc sql;
   select empid,jobcode,salary,
          salary*.06 as bonus
      from sasuser.payrollmaster
      where salary<32000
      order by jobcode;
Output report depicting EmpID, JobCode, Salary, and bonus
A PROC SQL query produces a result set that can be output as a report, a table, or a PROC SQL view.
Type of Output
PROC SQL Statement
report
SELECT
table
CREATE TABLE
PROC SQL view
CREATE VIEW
Note: The CREATE TABLE statement is introduced later in this chapter. You can learn about creating tables in Creating and Managing Tables Using PROC SQL. You can learn more about PROC SQL views in Creating and Managing Views Using PROC SQL.
You learn more about the SELECT  statement in the following sections.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
52.15.65.65