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

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

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