Introduction

The SELECT statement is the primary tool of PROC SQL. Using the SELECT statement, you can identify, manipulate, and retrieve columns of data from one or more tables and views.

You should already know how to create basic PROC SQL queries by using the SELECT statement and most of its subordinate clauses. To build on your existing skills, this lesson presents a variety of useful query techniques, such as the use of subqueries to subset data.

The PROC SQL query shown below illustrates some of the new query techniques that you will learn in this lesson:

 

     proc sql outobs=20;
     title 'Job Groups with Average Salary';
     title2 '> Company Average';
        select jobcode, 
               avg(salary) as AvgSalary format=dollar11.2,
               count(*) as Count
           from sasuser.payrollmaster
           group by jobcode
           having avg(salary) >
              (select avg(salary)
                 from sasuser.payrollmaster)
           order by avgsalary desc;

Job Groups with Average Salary
> Company Average

JobCode AvgSalary Count
PT3 $154,706.50 2
PT2 $122,253.40 10
PT1 $95,071.13 8
NA2 $73,336.00 3
ME3 $59,374.86 7
NA1 $58,845.00 5
TA3 $55,551.42 12


3 hours


In this lesson, you learn to

  • display all rows, eliminate duplicate rows, and limit the number of rows displayed
  • subset rows using other conditional operators and calculated values
  • enhance the formatting of query output
  • use summary functions, such as COUNT, with and without grouping
  • subset groups of data by using the HAVING clause
  • subset data by using correlated and noncorrelated subqueries
  • validate query syntax.

complete the following lessons:

  • .