Summary

Text Summary

PROC SQL Basics

PROC SQL uses statements that are written in Structured Query Language (SQL), which is a standardized language that is widely used to retrieve and update data in tables and in views that are based on those tables. When you want to examine relationships between data values, subset your data, or compute values, the SQL procedure provides an easy, flexible way to analyze your data.
PROC SQL differs from most other SAS procedures in several ways:
  • Many statements in PROC SQL, such as the SELECT statement, include clauses.
  • The PROC SQL step does not require a RUN statement.
  • PROC SQL continues to run after you submit a step. To end the procedure, you must submit another PROC step, a DATA step, or a QUIT statement.

Writing a PROC SQL Step

Before creating a query, you must assign a libref to the SAS library in which the table to be used is stored. Then you submit a PROC SQL step. You use the PROC SQL statement to invoke the SQL procedure.

Selecting Columns

To specify which column(s) to display in a query, you write a SELECT clause as the first clause in the SELECT statement. In the SELECT clause, you can specify existing columns and create new columns that contain either text or a calculation.

Specifying Tables

You specify the tables to be queried in the FROM clause.

Specifying Subsetting Criteria

To subset data based on a condition, write a WHERE clause that contains an expression.

Ordering Rows

The order of rows in the output of a PROC SQL query cannot be guaranteed, unless you specify a sort order. To sort rows by the values of specific columns, use the ORDER BY clause.

Querying Multiple Tables

You can use a PROC SQL step to query data that is stored in two or more tables. In SQL terminology, this is called joining tables. Follow these steps to join multiple tables:
  1. Specify column names from one or both tables in the SELECT clause and, if you are selecting a column that has the same name in multiple tables, prefix the table name to that column name.
  2. Specify each table name in the FROM clause.
  3. Use the WHERE clause to select rows from two or more tables, based on a condition.
  4. Use the ORDER BY clause to sort rows that are retrieved from two or more tables by the values of the selected column(s).

Summarizing Groups of Data

You can use a GROUP BY clause in your PROC SQL step to summarize data in groups. The GROUP BY clause is used in queries that include one or more summary functions. Summary functions produce a statistical summary for each group that is defined in the GROUP BY clause.

Creating Output Tables

To create a new table from the results of your query, you can use the CREATE TABLE statement in your PROC SQL step. This statement enables you to store your results in a table instead of displaying the query results as a report.

Additional Features

To further refine a PROC SQL query that contains a GROUP BY clause, you can use a HAVING clause. A HAVING clause works with the GROUP BY clause to restrict the groups that are displayed in the output, based on one or more specified conditions.

Sample Programs

Querying a Table

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

Summarizing Groups of Data

proc sql;
   select membertype,
          sum(milestraveled) as TotalMiles
      from sasuser.frequentflyers
      group by membertype;
quit;

Creating a Table from the Results of a Query on Two Tables

proc sql;
   create table work.miles as
      select salcomps.empid,lastname,
             newsals.salary,newsalary
      from sasuser.salcomps,sasuser.newsals
      where salcomps.empid=newsals.empid
      order by 2;
quit;

Points to Remember

  • Do not use a RUN statement with the SQL procedure.
  • Do not end a clause with a semicolon unless it is the last clause in the statement.
  • When you join multiple tables, be sure to specify columns that have matching data values in the WHERE clause.
  • To end the SQL procedure, you can submit another PROC step, a DATA step, or a QUIT statement.
..................Content has been hidden....................

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