Sample 25453: Creating and modifying tables and views
 |  |  |  |  |
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
These samples are from the "SAS Guide to the SQL Procedure, Version 6, First Edition" (Book No. 56070), Chapter 3. For additional information on the samples refer to this book.
/****************************************************************/
/* S A S S A M P L E L I B R A R Y */
/* */
/* NAME: SQLUG3 */
/* TITLE: Creating and Modifying Tables and Views, Chapter 3 */
/* PRODUCT: BASE */
/* SYSTEM: ALL */
/* KEYS: SQL DATMAN MACRO CREATE TABLE SELECT ORDER */
/* BY LIKE DROP VIEW SUM GROUP CONTENTS ALTER */
/* UPDATE SET CASE WHEN */
/* PROCS: SQL */
/* DATA: */
/* SUPPORT: UPDATE: */
/* REF: */
/* MISC: */
/* */
/****************************************************************/
data product;
input prodname $ 1-10 prodcost prodlist;
format prodcost prodlist dollar.;
cards;
flippers 16 20
jet ski 2150 2675
kayak 190 240
raft 5 7
snorkel 12 15
surfboard 615 750
windsurfer 1090 1325
run;
data customer;
input custname $ 1-10 custnum custcity $ 22-36;
cards;
Beach Land 16 Ocean City
Coast Shop 3 Myrtle Beach
Coast Shop 5 Myrtle Beach
Coast Shop 12 Virginia Beach
Coast Shop 14 Charleston
Del Mar 3 Folly Beach
Del Mar 8 Charleston
Del Mar 11 Charleston
New Waves 3 Ocean City
New Waves 6 Virginia Beach
Sea Sports 8 Charleston
Sea Sports 20 Virginia Beach
Surf Mart 101 Charleston
Surf Mart 118 Surfside
Surf Mart 127 Ocean Isle
Surf Mart 133 Charleston
run;
data employee;
input empnum empname $ empyears empcity $ 20-34
emptitle $ 36-45 empboss;
cards;
101 Herb 28 Ocean City president .
201 Betty 8 Ocean City manager 101
213 Joe 2 Virginia Beach salesrep 201
214 Jeff 1 Virginia Beach salesrep 201
215 Wanda 10 Ocean City salesrep 201
216 Fred 6 Ocean City salesrep 201
301 Sally 9 Wilmington manager 101
314 Marvin 5 Wilmington salesrep 301
318 Nick 1 Myrtle Beach salesrep 301
401 Chuck 12 Charleston manager 101
417 Sam 7 Charleston salesrep 401
run;
data invoice;
input invnum custname $ 6-15 custnum empnum prodname $ 28-37
invqty invprice;
format invprice dollar.;
cards;
280 Beach Land 16 215 snorkel 20 14
290 Beach Land 16 216 flippers 15 19
300 Beach Land 16 216 raft 20 7
310 Coast Shop 3 318 windsurfer 2 1305
320 Coast Shop 3 318 raft 30 6
330 Coast Shop 5 318 snorkel 5 15
340 Coast Shop 5 318 flippers 15 19
350 Coast Shop 5 318 raft 40 6
360 Coast Shop 5 318 snorkel 10 15
370 Coast Shop 12 213 raft 10 7
380 Coast Shop 14 417 windsurfer 1 1325
390 Del Mar 3 417 flippers 30 18
400 Del Mar 3 417 kayak 3 230
410 Del Mar 8 417 raft 40 6
420 Del Mar 11 417 raft 15 7
430 Del Mar 11 417 snorkel 10 15
440 Del Mar 11 417 flippers 20 19
450 New Waves 3 215 flippers 5 20
460 New Waves 3 215 flippers 10 20
470 New Waves 6 213 snorkel 15 15
480 New Waves 6 213 surfboard 4 735
490 New Waves 6 213 snorkel 10 15
500 Surf Mart 101 417 snorkel 20 14
510 Surf Mart 101 417 surfboard 2 740
520 Surf Mart 101 417 snorkel 12 15
530 Surf Mart 118 318 flippers 15 19
540 Surf Mart 118 318 raft 30 6
550 Surf Mart 118 318 snorkel 10 15
560 Surf Mart 127 314 flippers 25 19
570 Surf Mart 127 314 surfboard 3 740
run;
/* This example demonstrates how to create a temporary table from */
/* a permanent table. This table contains employees who have */
/* worked for the sample wholesale company for more than six years */
/* and who are salesreps. */
proc sql;
title2 'Senior staff table';
create table work.senior as
select empname, empyears, emptitle
from employee
where empyears >= 6 and emptitle = 'salesrep'
order by empyears desc;
select * from work.senior;
quit;
/* In this example the DROP data set option is used when creating */
/* a permanent table from an existing permanent table. The SUBINV */
/* table is a duplicate of the INVOICE table, with the exception */
/* of omitting the invnum and empnum columns. */
proc sql;
create table subinv(drop=invnum empnum)
like invoice;
select * from subinv;
quit;
/* This next example uses a column definition list (in parentheses) */
/* to define the columns and their attributes in the RIVALS table. */
/* The column attributes defined are data type, length, informat, */
/* and format. */
/* In the SELECT statement, the column sale_mon is defined with the */
/* format DOLLAR. No result table is displayed because the Rivals */
/* table contains no data. */
/* The rivals table is then dropped. */
proc sql;
create table rivals
(competit char(12), /* competitor's name */
compcity char(12), /* competitor's city */
custname char(12), /* customer's name */
prodname char(12), /* product name */
sale_mon num informat=dollar10.2 format=dollar.
/* competitor's monthly sales */
);
select competit, compcity, custname, prodname,
sale_mon format=dollar.
from rivals;
drop table rivals;
quit;
/* This example demonstrates how to create a temporary view */
/* BIGSALE and retrieve data from it. The view's SELECT statement */
/* lists invoices in which the total sale amount exceeds $1500. */
/* The Employee table is joined with the Invoice table so that the */
/* employee name, instead of employee number, can appear in the */
/* query result. */
proc sql;
create view bigsale as
select invnum, custname, custnum, empname,
prodname, invqty, invprice
from invoice as i, employee as e
where i.empnum = e.empnum and (invqty * invprice) > 1500;
title2 'Bigsale view sorted by invnum';
select * from bigsale order by invnum;
quit;
/* The statements below create and display a permanent view */
/* named HIGHQTY. The view is stored in a permanent SAS dataset */
/* by using a two-level name for it. */
/* Stored views can be described to the SAS log. */
proc sql;
create view highqty as
select * from invoice where invqty >= 25;
title2 'Highqty view';
select * from highqty;
describe view highqty;
quit;
/* This example demonstrates the FEEDBACK option. This option */
/* allows an expanded version of the view to be displayed, listing */
/* all the columns in the SELECT clause. */
proc sql feedback;
title2 'Highqty view';
select * from highqty;
reset nofeedback;
quit;
/* This example demonstrates how to delete a view using the DROP */
/* VIEW statement. The example creates and then deletes the Names */
/* view definition. */
proc sql;
create view names as
select prodname from product;
drop view names;
quit;
/* This example demonstrates an in-line view, join, and summary */
/* function. The example lists the salesreps who have sold 20 or */
/* more rafts and the number of rafts that each sold. */
proc sql;
title2 'Salesreps who have sold 20 or more rafts';
select empname, numraft
from ( select empname, sum(invqty) as numraft
from invoice i, employee e
where prodname = 'raft' and i.empnum = e.empnum
group by empname
)
where numraft >= 20
order by 1;
quit;
/* The example below uses the PRINT procedure to display the data */
/* defined by the Highqty view. All SAS procedures can process SQL */
/* views as if they were SAS datasets. Notice the differences */
/* between the CONTENTS of the Highqty table and that of the */
/* Highqty's underlying table, Invoice. */
title2 'Highqty view with SAS Procedures';
proc print data=highqty noobs;
run;
title2 'Description of Highqty view';
proc contents data=highqty;
run;
title2 'Description of Invoice table';
proc contents data=invoice;
run;
/* SQL for Data Management */
/* The following example creates a permanent table, NEWPRICE. */
/* It then adds a new column to that table. */
proc sql;
title2 'Newprice table';
create table newprice as
select prodname as product, prodcost as cost,
prodlist label='list price'
from product;
alter table newprice
add list1989 num format=dollar.;
select * from newprice;
quit;
/* An alternative way of creating the newprice table is to compute */
/* the new price column as you create the table. */
proc sql;
create table newprice as
select prodname as product, prodcost as cost,
prodlist label='List Price',
(prodlist * 1.2) as list1989 format=dollar.
from product;
select * from newprice;
quit;
/* This example demonstrates using the ALTER statement to change */
/* the format of the columns. */
proc sql;
alter table newprice
modify cost num format=dollar10.2,
prodlist num format=dollar10.2,
list1989 num format=dollar10.2;
title2 'Newprice table with new formats';
select * from newprice;
quit;
/* This example demonstrates using the ALTER statement to drop a */
/* column. */
proc sql;
alter table newprice
drop cost;
title2 'Newprice table without cost';
select * from newprice;
quit;
/* This example demonstrates an UPDATE statement with and without */
/* a WHERE expression. In the first UPDATE statement below all */
/* values are updated int the LIST1989 column because the WHERE */
/* expression was omitted. In the second UPDATE statement, the */
/* WHERE expression eliminates rows that have values greater than */
/* 240 in the PRODLIST column. The third UPDATE statement supplies */
/* values for products whose prices were not changed by the first */
/* statement. */
/* Note that this approach requires two passes over the dataset, */
/* one for each update statement. */
proc sql;
create table newprice as
select prodname as product, prodcost as cost,
prodlist label='list price'
from product;
alter table newprice
add list1989 num format=dollar.;
update newprice
set list1989 = prodlist * 1.2;
title2 'Newprice table after udpate';
select * from newprice;
update newprice
set list1989 = prodlist * 1.2
where prodlist <= 240;
update newprice
set list1989 = prodlist
where prodlist > 240;
select * from newprice;
quit;
/* This example uses a CASE expression, and effects the update in */
/* a single pass over the newprice dataset. The case expression */
/* returns the multiplier to be used to compute list1989 from */
/* prodlist. */
proc sql;
update newprice
set list1989 = prodlist *
case when prodlist <= 240 then 1.2
else 1
end
;
title2 'Newprice table using a case expression';
select * from newprice;
quit;
/* This example demonstrates the INSERT statement to insert rows. */
/* You can insert constant values, or the results for some query */
/* expression. */
/* It also demonstrates the delete statement to remove rows from */
/* a table. */
proc sql;
create table newprice as
select prodname as product, prodcost as cost,
prodlist label='list price',
(prodlist * 1.2) as list1989 format=dollar.
from product;
insert into newprice
set product = 'umbrella',
cost = 12,
prodlist = 15,
list1989 = 18;
title2 'Newprice table after insert';
select * from newprice;
insert into newprice
values('umbrella',12,15,18);
/* The default value of the UNDO_POLICY option will */
/* cause PROC SQL to obtain exclusive access to the */
/* dataset being inserted into (newprice). */
/* The second reference to the same table will fail. */
/* Choosing UNDO_POLICY=OPTIONAL allows this query to proceed. */
/* For details on the UNDO_POLICY option, please refer to the */
/* 607 changes and enhancements documentation. */
reset undo_policy=optional;
insert into newprice
select trim(product) || " GT", cost * 1.2 as cost,
prodlist * 1.3 as prodlist,
list1989 * 1.3 as list1989
from newprice
where product in ('raft','kayak');
select * from newprice;
/* Reset to default undo_policy=required */
reset undo_policy=required;
delete
from newprice
where product = 'umbrella';
quit;
/* The example demonstrates the RESET option which is used to add, */
/* drop, or change the options on the PROC SQL statement. */
title;
proc sql noprint;
select * from product;
reset print number;
select * from product
where prodcost < 20;
quit;
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
Senior staff table
EMPNAME EMPYEARS EMPTITLE
------------------------------
Wanda 10 salesrep
Sam 7 salesrep
Fred 6 salesrep
Bigsale view sorted by invnum
INVNUM CUSTNAME CUSTNUM EMPNAME PRODNAME INVQTY INVPRICE
------------------------------------------------------------------------
310 Coast Shop 3 Nick windsurfer 2 $1,305
480 New Waves 6 Joe surfboard 4 $735
570 Surf Mart 127 Marvin surfboard 3 $740
Highqty view
INVNUM CUSTNAME CUSTNUM EMPNUM PRODNAME INVQTY INVPRICE
------------------------------------------------------------------------
320 Coast Shop 3 318 raft 30 $6
350 Coast Shop 5 318 raft 40 $6
390 Del Mar 3 417 flippers 30 $18
410 Del Mar 8 417 raft 40 $6
540 Surf Mart 118 318 raft 30 $6
560 Surf Mart 127 314 flippers 25 $19
Highqty view
INVNUM CUSTNAME CUSTNUM EMPNUM PRODNAME INVQTY INVPRICE
------------------------------------------------------------------------
320 Coast Shop 3 318 raft 30 $6
350 Coast Shop 5 318 raft 40 $6
390 Del Mar 3 417 flippers 30 $18
410 Del Mar 8 417 raft 40 $6
540 Surf Mart 118 318 raft 30 $6
560 Surf Mart 127 314 flippers 25 $19
Salesreps who have sold 20 or more rafts
EMPNAME NUMRAFT
------------------
Fred 20
Nick 100
Sam 55
Highqty view with SAS Procedures
INVNUM CUSTNAME CUSTNUM EMPNUM PRODNAME INVQTY INVPRICE
320 Coast Shop 3 318 raft 30 $6
350 Coast Shop 5 318 raft 40 $6
390 Del Mar 3 417 flippers 30 $18
410 Del Mar 8 417 raft 40 $6
540 Surf Mart 118 318 raft 30 $6
560 Surf Mart 127 314 flippers 25 $19
Description of Highqty view
CONTENTS PROCEDURE
Data Set Name: WORK.HIGHQTY Observations: .
Member Type: VIEW Variables: 7
Engine: SASESQL Indexes: 0
Created: 15:39 Wednesday, June 29, 2005 Observation Length: 72
Last Modified: 15:39 Wednesday, June 29, 2005 Deleted Observations: 0
Protection: Compressed: NO
Data Set Type: Sorted: NO
Label:
-----Alphabetic List of Variables and Attributes-----
# Variable Type Len Pos Format
----------------------------------------------
2 CUSTNAME Char 10 8
3 CUSTNUM Num 8 24
4 EMPNUM Num 8 32
1 INVNUM Num 8 0
7 INVPRICE Num 8 64 DOLLAR.
6 INVQTY Num 8 56
5 PRODNAME Char 10 40
Description of Invoice table
CONTENTS PROCEDURE
Data Set Name: WORK.INVOICE Observations: 30
Member Type: DATA Variables: 7
Engine: V612 Indexes: 0
Created: 15:39 Wednesday, June 29, 2005 Observation Length: 60
Last Modified: 15:39 Wednesday, June 29, 2005 Deleted Observations: 0
Protection: Compressed: NO
Data Set Type: Sorted: NO
Label:
-----Engine/Host Dependent Information-----
Data Set Page Size: 8192
Number of Data Set Pages: 1
File Format: 607
First Data Page: 1
Max Obs per Page: 135
Obs in First Data Page: 30
-----Alphabetic List of Variables and Attributes-----
# Variable Type Len Pos Format
----------------------------------------------
2 CUSTNAME Char 10 8
3 CUSTNUM Num 8 18
4 EMPNUM Num 8 26
1 INVNUM Num 8 0
7 INVPRICE Num 8 52 DOLLAR.
6 INVQTY Num 8 44
5 PRODNAME Char 10 34
Newprice table
list
PRODUCT COST price LIST1989
------------------------------------
flippers $16 $20 .
jet ski $2,150 $2,675 .
kayak $190 $240 .
raft $5 $7 .
snorkel $12 $15 .
surfboard $615 $750 .
windsurfer $1,090 $1,325 .
Newprice table
List
PRODUCT COST Price LIST1989
------------------------------------
flippers $16 $20 $24
jet ski $2,150 $2,675 $3,210
kayak $190 $240 $288
raft $5 $7 $8
snorkel $12 $15 $18
surfboard $615 $750 $900
windsurfer $1,090 $1,325 $1,590
Newprice table with new formats
PRODUCT COST List Price LIST1989
----------------------------------------------
flippers $16.00 $20.00 $24.00
jet ski $2,150.00 $2,675.00 $3,210.00
kayak $190.00 $240.00 $288.00
raft $5.00 $7.00 $8.40
snorkel $12.00 $15.00 $18.00
surfboard $615.00 $750.00 $900.00
windsurfer $1,090.00 $1,325.00 $1,590.00
Newprice table without cost
PRODUCT List Price LIST1989
----------------------------------
flippers $20.00 $24.00
jet ski $2,675.00 $3,210.00
kayak $240.00 $288.00
raft $7.00 $8.40
snorkel $15.00 $18.00
surfboard $750.00 $900.00
windsurfer $1,325.00 $1,590.00
Newprice table after udpate
list
PRODUCT COST price LIST1989
------------------------------------
flippers $16 $20 $24
jet ski $2,150 $2,675 $3,210
kayak $190 $240 $288
raft $5 $7 $8
snorkel $12 $15 $18
surfboard $615 $750 $900
windsurfer $1,090 $1,325 $1,590
Newprice table after udpate
list
PRODUCT COST price LIST1989
------------------------------------
flippers $16 $20 $24
jet ski $2,150 $2,675 $2,675
kayak $190 $240 $288
raft $5 $7 $8
snorkel $12 $15 $18
surfboard $615 $750 $750
windsurfer $1,090 $1,325 $1,325
Newprice table using a case expression
list
PRODUCT COST price LIST1989
------------------------------------
flippers $16 $20 $24
jet ski $2,150 $2,675 $2,675
kayak $190 $240 $288
raft $5 $7 $8
snorkel $12 $15 $18
surfboard $615 $750 $750
windsurfer $1,090 $1,325 $1,325
Newprice table after insert
list
PRODUCT COST price LIST1989
------------------------------------
flippers $16 $20 $24
jet ski $2,150 $2,675 $3,210
kayak $190 $240 $288
raft $5 $7 $8
snorkel $12 $15 $18
surfboard $615 $750 $900
windsurfer $1,090 $1,325 $1,590
umbrella $12 $15 $18
Newprice table after insert
list
PRODUCT COST price LIST1989
------------------------------------
flippers $16 $20 $24
jet ski $2,150 $2,675 $3,210
kayak $190 $240 $288
raft $5 $7 $8
snorkel $12 $15 $18
surfboard $615 $750 $900
windsurfer $1,090 $1,325 $1,590
umbrella $12 $15 $18
umbrella $12 $15 $18
kayak GT $228 $312 $374
raft GT $6 $9 $11
Row PRODNAME PRODCOST PRODLIST
--------------------------------------
1 flippers $16 $20
2 raft $5 $7
3 snorkel $12 $15
Creating and modifying tables and views.
| Type: | Sample |
| Topic: | SAS Reference ==> Procedures ==> SQL |
| Date Modified: | 2005-08-27 03:03:17 |
| Date Created: | 2005-05-23 13:54:47 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | All | n/a | n/a |