SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

Input Tables

This example creates a new table, Proclib.Newpay, by concatenating two other tables:

Proclib.Paylist and Proclib.Paylist2.

proc sql;

title 'Proclib.Paylist Table';

select * from proclib.paylist;

Figure 7.5 Proclib.Paylist Table

proc sql;

title 'Proclib.Paylist2 Table';

select * from proclib.Paylist2;

title;

Figure 7.6 Proclib.Paylist2 Table

Program

libname proclib 'SAS-library';

proc sql;

create table proclib.newpay as

select * from proclib.paylist

union

select * from proclib.paylist2;

Example 5: Combining Two Tables 279

delete

from proclib.newpay

where jobcode is missing or salary is missing;

reset double;

title 'Personnel Data';

select *

from proclib.newpay;

Program Description

Declare the Proclib library. The Proclib library is used in these examples to store

created tables.

libname proclib 'SAS-library';

Create the Proclib.Newpay table. The SELECT clauses select all the columns from the

tables that are listed in the FROM clauses. The UNION set operator concatenates the

query results that are produced by the two SELECT clauses.

proc sql;

create table proclib.newpay as

select * from proclib.paylist

union

select * from proclib.paylist2;

Delete rows with missing Jobcode or Salary values. The DELETE statement deletes

rows from Proclib.Newpay that satisfy the WHERE expression. The IS condition

specifies rows that contain missing values in the Jobcode or Salary column.

delete

from proclib.newpay

where jobcode is missing or salary is missing;

Reset the PROC SQL environment and double-space the output. RESET changes

the procedure environment without stopping and restarting PROC SQL. The DOUBLE

option double-spaces the output. (The DOUBLE option has no effect on ODS output.)

reset double;

Specify the title.

title 'Personnel Data';

Display the entire Proclib.Newpay table. The SELECT clause selects all columns from

the newly created table, Proclib.Newpay.

select *

from proclib.newpay;

280 Chapter 7 • SQL Procedure

Output: Combining Two Tables

Output 7.7 The Proclib.Newpay Table

Example 6: Reporting from DICTIONARY Tables

Features: DESCRIBE TABLE statement

DICTIONARY.table-name component

Table name: DICTIONARY.Members

This example uses DICTIONARY tables to show a list of the SAS files in a SAS library.

If you do not know the names of the columns in the DICTIONARY table that you are

querying, then use a DESCRIBE TABLE statement with the table.

Program

libname proclib 'SAS-library';

proc sql;

describe table dictionary.members;

title 'SAS Files in the Proclib Library';

select memname, memtype

from dictionary.members

where libname='PROCLIB';

Program Description

Declare the Proclib library. The Proclib library is used in these examples to store

created tables.

libname proclib 'SAS-library';

Example 6: Reporting from DICTIONARY Tables 281

List the column names from the DICTIONARY.Members table. DESCRIBE TABLE

writes the column names from DICTIONARY.Members to the SAS log.

proc sql;

describe table dictionary.members;

Specify the title.

title 'SAS Files in the Proclib Library';

Display a list of files in the Proclib library. The SELECT clause selects the

MEMNAME and MEMTYPE columns. The FROM clause specifies

DICTIONARY.Members as the table to select from. The WHERE clause subsets the

output to include only those rows that have a libref of Proclib in the LIBNAME column.

select memname, memtype

from dictionary.members

where libname='PROCLIB';

Log

Log 7.1 Creating Table DICTIONARY.Members Log

277 options nodate pageno=1 source linesize=80 pagesize=60;

278

279 proc sql;

280 describe table dictionary.members;

NOTE: SQL table DICTIONARY.Members was created like:

create table DICTIONARY.Members

(

libname char(8) label='Library Name',

memname char(32) label='Member Name',

memtype char(8) label='Member Type',

engine char(8) label='Engine Name',

index char(32) label='Indexes',

path char(1024) label='Path Name'

);

281 title 'SAS Files in the Proclib Library';

282

283 select memname, memtype

284 from dictionary.members

285 where libname='PROCLIB';

282 Chapter 7 • SQL Procedure

Output: SAS Files in the Proclib Library

Output 7.8 The Proclib Library

Example 7: Performing an Outer Join

Features: joined-table component

left outer join

SELECT clause

COALESCE function

WHERE clause

CONTAINS condition

Table names: Proclib.Payroll

Proclib.Payroll2

Details

This example illustrates a left outer join of the Proclib.Payroll and Proclib.Payroll2

tables.

proc sql outobs=10;

title 'Proclib.Payroll';

title2 'First 10 Rows Only';

select * from proclib.payroll

order by idnumber;

title;

Example 7: Performing an Outer Join 283