O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required