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

Examples: SQL Procedure

Example 1: Creating a Table and Inserting Data into It

Features: CREATE TABLE statement

column-modifier

INSERT statement

VALUES clause

SELECT clause

FROM clause

Table name: Proclib.Paylist

This example creates the table Proclib.Paylist and inserts data into it.

Program

libname proclib 'SAS-library';

proc sql;

create table proclib.paylist

(IdNum char(4),

Gender char(1),

Jobcode char(3),

Salary num,

Birth num informat=date7.

format=date7.,

Hired num informat=date7.

format=date7.);

insert into proclib.paylist

values('1639','F','TA1',42260,'26JUN70'd,'28JAN91'd)

values('1065','M','ME3',38090,'26JAN54'd,'07JAN92'd)

values('1400','M','ME1',29769.'05NOV67'd,'16OCT90'd)

values('1561','M',null,36514,'30NOV63'd,'07OCT87'd)

values('1221','F','FA3',.,'22SEP63'd,'04OCT94'd);

title 'Proclib.Paylist Table';

select *

from proclib.paylist;

proc printto; run;

Program Description

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

created tables.

libname proclib 'SAS-library';

Example 1: Creating a Table and Inserting Data into It 269

Create the Proclib.Paylist table. The CREATE TABLE statement creates

Proclib.Paylist with six empty columns. Each column definition indicates whether the

column is character or numeric. The number in parentheses specifies the width of the

column. INFORMAT= and FORMAT= assign date informats and formats to the Birth

and Hired columns.

proc sql;

create table proclib.paylist

(IdNum char(4),

Gender char(1),

Jobcode char(3),

Salary num,

Birth num informat=date7.

format=date7.,

Hired num informat=date7.

format=date7.);

Insert values into the Proclib.Paylist table. The INSERT statement inserts data values

into Proclib.Paylist according to the position in the VALUES clause. Therefore, in the

first VALUES clause, 1639 is inserted into the first column, F into the second column,

and so on. Dates in SAS are stored as integers with 0 equal to January 1, 1960. Suffixing

the date with a

d is one way to use the internal value for dates.

insert into proclib.paylist

values('1639','F','TA1',42260,'26JUN70'd,'28JAN91'd)

values('1065','M','ME3',38090,'26JAN54'd,'07JAN92'd)

values('1400','M','ME1',29769.'05NOV67'd,'16OCT90'd)

Include missing values in the data. The value null represents a missing value for the

character column Jobcode. The period represents a missing value for the numeric

column Salary.

values('1561','M',null,36514,'30NOV63'd,'07OCT87'd)

values('1221','F','FA3',.,'22SEP63'd,'04OCT94'd);

Specify the title.

title 'Proclib.Paylist Table';

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

Proclib.Paylist. The asterisk (*) selects all columns. The FROM clause specifies

Proclib.Paylist as the table to select from.

select *

from proclib.paylist;

proc printto; run;

270 Chapter 7 • SQL Procedure

Output: Inserting Data into a Table

Output 7.2 The Proclib.Paylist Table

Example 2: Creating a Table from a Query's Result

Features: CREATE TABLE statement

AS query expression

SELECT clause

columnalias

FORMAT=column-modifier

object-item

Other features: Data Set Option

OBS=

Table names: Proclib.Payroll

Proclib.Bonus

Details

This example builds a column with an arithmetic expression and creates the

Proclib.Bonus table from the query's result.

proc sql outobs=10;

title 'Proclib.Payroll';

title2 'First 10 Rows Only';

select * from proclib.payroll;

title;

Example 2: Creating a Table from a Query's Result 271

Figure 7.2 Query Result from Proclib.Payroll

Program

libname proclib 'SAS-library';

proc sql;

create table proclib.bonus as

select IdNumber, Salary format=dollar8.,

salary*.025 as Bonus format=dollar8.

from proclib.payroll;

title 'Bonus Information';

select *

from proclib.bonus(obs=10);

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.Bonus table. The CREATE TABLE statement creates the table

Proclib.Bonus from the result of the subsequent query.

proc sql;

create table proclib.bonus as

Select the columns to include. The SELECT clause specifies that three columns will

be in the new table: IdNumber, Salary, and Bonus. FORMAT= assigns the DOLLAR8.

format to Salary. The Bonus column is built with the SQL expression salary*.025.

272 Chapter 7 • SQL Procedure

select IdNumber, Salary format=dollar8.,

salary*.025 as Bonus format=dollar8.

from proclib.payroll;

Specify the title.

title 'Bonus Information';

Display the first 10 rows of the Proclib.Bonus table. The SELECT clause selects

columns from Proclib.Bonus. The asterisk (*) selects all columns. The FROM clause

specifies Proclib.Bonus as the table to select from. The OBS= data set option limits the

printing of the output to 10 rows.

select *

from proclib.bonus(obs=10);

Output: Creating a Table from a Query

Output 7.3 The Proclib.Bonus Table

Example 3: Updating Data in a PROC SQL Table

Features: ALTER TABLE statement

DROP clause

MODIFY clause

UPDATE statement

SET clause

CASE expression

Table name: Employees

Example 3: Updating Data in a PROC SQL Table 273