Providing software solutions since 1976
Sample 25635: Using the advanced features of the SQL procedure
 |  |  |  |  |
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 4.
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: SQLUG4 */
/* TITLE: Using the Advanced Features of the SQL Procedure, */
/* Chapter 4 */
/* PRODUCT: BASE */
/* SYSTEM: ALL */
/* KEYS: SQL DATMAN MACRO CREATE TABLE SELECT ORDER */
/* BY DROP VIEW SUM GROUP SET */
/* WHEN AVG HAVING RESET DELETE */
/* UNDO_POLICY COALESCE FULL JOIN DISTINCT */
/* 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;
/* SQL and summary statistics */
/* In this example, a summary function is used alone in the */
/* SELECT clause so it is evaluated without remerging the data. */
proc sql;
title2 'Average years of employment';
select avg(empyears) as avg
from employee;
/* In this example, the data is not remerged because the average- */
/* number-of-years values is associated with each row of the */
/* GROUP BY item, EMPTITLE. */
select emptitle, avg(empyears) as avg
from employee
group by emptitle;
/* This example demonstrates how it is possible to get unexpected */
/* results from a query that remerges data. This query calculates */
/* the average based on all the EMPYEARS (that is, EMPYEARS is */
/* treated as one group). */
title2 'Average years of employment';
select emptitle, avg(empyears) as avg
from employee;
/* This is an example of when data is REMERGED. This remerging is */
/* useful when you wish to compute "as a fraction of the total" */
/* type of expressions. Here we are interested in the contribution */
/* of each customer to our total revenue, expressed as a percentage. */
title2 'Invoice information sorted by store';
select custname, custnum, invnum,
(100*invqty*invprice)/sum(invqty*invprice) as percent format=9.6
from invoice
group by custname
order by custname, percent desc;
/* This example combines a number of components including summary */
/* functions, a HAVING expression with a subquery, and an in-line */
/* view within that subquery. */
title2 'Employee cities with the greatest dollar sales';
select e.empcity, sum(invqty*invprice) as sales format=dollar.
from employee e, invoice i
where e.empnum = i.empnum
group by empcity
having sales = ( select max(sales)
from ( select e.empcity,
sum(invqty*invprice) as sales
from employee e, invoice i
where e.empnum = i.empnum
group by empcity
)
)
order by 1;
/* SQL and set operations */
/* This example uses the UNION operator to list products that were */
/* sold to stores in Myrtle Beach or sold by salesreps who live in */
/* Virginia Beach or both. Duplicate rows are automatically */
/* eliminated from the result table. */
title2 'Product information using UNION';
select 'Salesrep in Virginia Beach' as who, prodname as product
from invoice
where 'Virginia Beach' = ( select empcity
from employee
where empnum = invoice.empnum
)
union
select 'Store in Myrtle Beach' as who, prodname as product
from invoice
where 'Myrtle Beach' = ( select custcity
from customer
where custname = invoice.custname
and custnum = invoice.custnum
)
order by 1, 2;
/* This example uses the EXCEPT operator to list products that were */
/* sold by salesreps who live in Virginia Beach but were not sold */
/* to stores in Myrtle Beach. */
title2 'Product information using EXCEPT';
select prodname as product
from invoice
where 'Virginia Beach' = ( select empcity
from employee
where empnum = invoice.empnum
)
except
select prodname as product
from invoice
where 'Myrtle Beach' = ( select custcity
from customer
where custname = invoice.custname
and custnum = invoice.custnum
)
order by 1;
/* SQL and n-way joins */
/* This example shows how a join on four tables (a four-way join) */
/* is performed. The following query shows sales figures of items */
/* priced over $150 that were sold by each manager's group */
/* (salesreps) in the sample wholesale company. */
title2 'Sales figures';
select e1.empname as manager,
count(i.invnum) as numsales,
sum(i.invqty * i.invprice) as totsales,
sum(i.invqty * p.prodcost) as totcost,
sum(i.invqty * (i.invprice - p.prodcost)) as totmarg,
sum(i.invqty * (i.invprice - p.prodcost)) /
sum(i.invqty * i.invprice) as pctmarg
from invoice i, product p,
employee e1, employee e2
where i.prodname = p.prodname
and p.prodcost > 150
and i.empnum = e2.empnum
and e1.emptitle = 'manager'
and e1.empnum = e2.empboss
group by manager;
/* These next three examples break down the four-way join */
/* into separate(intermediate) two-way joins. */
create table t1 as
select p.prodcost, i.empnum, i.invnum, i.invqty, i.invprice
from invoice i, product p
where i.prodname = p.prodname
and p.prodcost > 150;
title2 'Intermediate result T1';
select * from t1;
create table t2 as
select prodcost, empboss, invnum, invqty, invprice
from t1, employee e
where t1.empnum = e.empnum;
title2 'Intermediate result T2';
select * from t2;
create table t3 as
select prodcost, empname as manager, invnum, invqty, invprice
from t2, employee e
where t2.empboss = e.empnum and e.emptitle = 'manager';
title2 'Intermediate result T3';
select * from t3;
/* This example is the final breakdown of the four-way join. The */
/* intermediate table T3 now includes only those rows that satisfy */
/* the conditions in the original query's WHERE expression. The */
/* query can now be evaluated using the T3 table. */
title2 'Final result';
select manager,
count(invnum) as numsales,
sum(invqty * invprice) as totsales,
sum(invqty * prodcost) as totcost,
sum(invqty * (invprice - prodcost)) as totmarg,
sum(invqty * (invprice - prodcost)) /
sum(invqty * invprice) as pctmarg
from t3
group by manager;
/* SQL and indexes */
/* This example defines INDEXES on two columns in the INVOICE */
/* table. The option STIMER is used to demonstrate the */
/* efficiency of indexes. */
reset stimer;
title2 'Before indexes are defined';
select e1.empname as manager,
count(i.invnum) as numsales,
sum(i.invqty * i.invprice) as totsales,
sum(i.invqty * p.prodcost) as totcost,
sum(i.invqty * (i.invprice - p.prodcost)) as totmarg,
sum(i.invqty * (i.invprice - p.prodcost)) /
sum(i.invqty * i.invprice) as pctmarg
from invoice i, product p,
employee e1, employee e2
where i.prodname = p.prodname
and p.prodcost > 150
and i.empnum = e2.empnum
and e1.emptitle = 'manager'
and e1.empnum = e2.empboss
group by manager;
create index empnum on invoice(empnum);
create index prodname on invoice(prodname);
title2 'After indexes are defined';
select e1.empname as manager,
count(i.invnum) as numsales,
sum(i.invqty * i.invprice) as totsales,
sum(i.invqty * p.prodcost) as totcost,
sum(i.invqty * (i.invprice - p.prodcost)) as totmarg,
sum(i.invqty * (i.invprice - p.prodcost)) /
sum(i.invqty * i.invprice) as pctmarg
from invoice i, product p,
employee e1, employee e2
where i.prodname = p.prodname
and p.prodcost > 150
and i.empnum = e2.empnum
and e1.emptitle = 'manager'
and e1.empnum = e2.empboss
group by manager;
drop index empnum, prodname from invoice;
/* This example shows that performance can be enhanced when */
/* indexes are defined on columns that participate in equijoins */
/* on a frequent basis. */
data twoK; do i = 1 to 2000; output; end; run;
data fiftyK; do i = 1 to 50000; output; end; run;
proc sql stimer;
title2 'Before index created';
select count(*) from twoK t, fiftyK f where t.i=f.i;
create unique index i on fiftyK(i);
title2 'After index created';
select count(*) from twoK t, fiftyK f where t.i=f.i;
/* SQL and outer joins */
/* In this example, a LEFT OUTER JOIN is used to list all the */
/* products and sales (if any) of those products to the */
/* Beach Land store. */
proc sql;
title2 'Beach Land store sales';
select p.prodname, i.invqty, i.invprice, p.prodlist,
i.invqty*i.invprice format=dollar. label='Invoice Amount'
from product p left join invoice i
on p.prodname=i.prodname and i.custname='Beach Land';
/* This next example creates four tables and performs a FULL OUTER */
/* JOIN on them. The COALESCE function returns the first argument */
/* whose value is not a SAS missing value. */
data j1;
input x1 j1$ @@;
cards;
0 j1_0 1 j1_1
data j2;
input x2 j2$ @@;
cards;
0 j2_0 2 j2_2
data j3;
input x3 j3$ @@;
cards;
0 j3_0 3 j3_3
data j4;
input x4 j4$ @@;
cards;
0 j4_0 4 j4_4
;
proc sql;
title2;
select coalesce(x1,x2,x3,x4) as x,j1,j2,j3,j4
from j1 full join j2 on x1=x2
full join j3 on x1=x3
full join j4 on x1=x4;
select coalesce(x1,x2,x3,x4) as x,j1,j2,j3,j4
from j1 full join j2 on x1=x2
full join j3 on x1=x3
full join j4 on x1=x4
order by 1;
/* Equivalent SQL queries */
/* The next three examples demonstrate how different queries can */
/* produce the same results. Each of the following queries lists */
/* the customers who have the greatest number of stores. */
/* Query one compares a correlated subquery with a subquery that */
/* includes an in-line view in the WHERE expression. */
/* Query two uses one subquery with an in-line view as the */
/* predicate in the HAVING expression. */
/* Query Three creates a temporary table and performs a query */
/* on it. */
reset stimer;
title2 'Query one';
select distinct custname
from customer c1
where ( select count(*)
from customer c2
where c2.custname = c1.custname
)
=
( select max(numstore)
from ( select count(*) as numstore
from customer
group by custname
)
)
order by 1;
title2 'Query two';
select distinct custname, count(*)
from customer c1
group by custname
having count(*) = ( select max(numstore)
from ( select count(*) as numstore
from customer
group by custname
)
)
order by 1;
title2 'Query three';
create table temp as
select custname, count(*) as numstore
from customer
group by custname;
select distinct custname, numstore
from temp
where numstore = (select max(numstore) from temp)
order by 1;
reset nostimer;
/* SQL and SAS macro language */
/* This example creates a table listing people who are qualified */
/* to serve as referees for a review of academic papers. No more */
/* than three people per subject are required in a table. The SAS */
/* macro language is used to check the count of those people */
/* qualified to referee a subject before inserting a new person. */
proc sql;
create table referee
( name char(15),
subject char(15)
);
/* This example defines a SAS macro to insert people into the */
/* table. The macro has two parameters: the person's name and */
/* the subject he is qualified to referee. */
%macro addref(name,subject);
%local count;
/* Do we have three people who can referee this subject? */
reset noprint;
select count(*)
into :count
from referee
where subject = "&subject";
/* Yes, we do. So lets show the user who they are.. */
%if &count >= 3 %then %do;
reset print;
title2 "ERROR: &name not inserted for subject - &subject..";
title3 " There are 3 referees already.";
select * from referee where subject = "&subject";
reset noprint;
%end;
/* Nope, so lets add this person as a referee.. */
%else %do;
insert into referee(name,subject)
values("&name","&subject");
%put Note: &name has been added for subject - &subject..;
%end;
%mend;
/* We now use our macro to add people to the table. */
%addref(Conner,sailing);
%addref(Fay,sailing);
%addref(Einstein,relativity);
%addref(Smythe,sailing);
%addref(Naish,sailing);
quit;
/* SQL and SAS/FSP SCL Language. */
/* The following example assumes you have built your primary menu */
/* screen and are using SCL to write the source portion of your */
/* program entry. The SAS/AF program screen is designed with two */
/* fields, name and subject. The program associated with the screen */
/* is as follows: */
/* Use this code as the SCL source */
/* Do we have three people who can referee this subject? */
/* If we do, tell the user. */
/* If we do not, add this person as a referee. */
/*
INIT:
control always;
return;
MAIN:
submit continue sql;
proc sql;
reset noprint;
select count(*)
into :count
from referee
where subject = "&subject";
endsubmit;
if symget('count') >= 3 then
_msg_ = 'ERROR: ' || trim(name) || ' not inserted.';
else do;
submit continue sql;
insert into referee values("&name","&subject");
endsubmit;
_msg_ = "NOTE: ' || trim(name) || ' has beed added for '
|| subject";
end;
TERM:
End of SCL Source */
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.
Bold titles are for clarification purpose only.
They were not generated by the SAS System.
SQL and summary statistics
Average years of employment
AVG
--------
8.090909
Average years of employment
EMPTITLE AVG
--------------------
manager 9.666667
president 28
salesrep 4.571429
Average years of employment
EMPTITLE AVG
--------------------
president 8.090909
manager 8.090909
salesrep 8.090909
salesrep 8.090909
salesrep 8.090909
salesrep 8.090909
manager 8.090909
salesrep 8.090909
salesrep 8.090909
manager 8.090909
salesrep 8.090909
Invoice information sorted by store
CUSTNAME CUSTNUM INVNUM PERCENT
-----------------------------------------
Beach Land 16 290 40.425532
Beach Land 16 280 39.716312
Beach Land 16 300 19.858156
Coast Shop 3 310 52.887538
Coast Shop 14 380 26.849037
Coast Shop 5 340 5.775076
Coast Shop 5 350 4.863222
Coast Shop 3 320 3.647416
Coast Shop 5 360 3.039514
Coast Shop 5 330 1.519757
Coast Shop 12 370 1.418440
Del Mar 3 400 32.779097
Del Mar 3 390 25.653207
Del Mar 11 440 18.052257
Del Mar 8 410 11.401425
Del Mar 11 430 7.125891
Del Mar 11 420 4.988124
New Waves 6 480 81.327801
New Waves 6 470 6.224066
New Waves 3 460 5.532503
New Waves 6 490 4.149378
New Waves 3 450 2.766252
Surf Mart 127 570 42.285714
Surf Mart 101 510 28.190476
Surf Mart 127 560 9.047619
Surf Mart 118 530 5.428571
Surf Mart 101 500 5.333333
Surf Mart 101 520 3.428571
Surf Mart 118 540 3.428571
Surf Mart 118 550 2.857143
Employee cities with the greatest dollar sales
EMPCITY SALES
-----------------------
Charleston $5,370
SQL and set operations
Product information using UNION
WHO PRODUCT
--------------------------------------
Salesrep in Virginia Beach raft
Salesrep in Virginia Beach snorkel
Salesrep in Virginia Beach surfboard
Store in Myrtle Beach flippers
Store in Myrtle Beach raft
Store in Myrtle Beach snorkel
Store in Myrtle Beach windsurfer
Product information using EXCEPT
PRODUCT
----------
surfboard
SQL and n-way joins
Sales figures
MANAGER NUMSALES TOTSALES TOTCOST TOTMARG PCTMARG
----------------------------------------------------------
Betty 1 2940 2460 480 0.163265
Chuck 3 3495 2890 605 0.173104
Sally 2 4830 4025 805 0.166667
Intermediate result T1
PRODCOST EMPNUM INVNUM INVQTY INVPRICE
------------------------------------------------
$1,090 318 310 2 $1,305
$1,090 417 380 1 $1,325
$190 417 400 3 $230
$615 213 480 4 $735
$615 417 510 2 $740
$615 314 570 3 $740
Intermediate result T2
PRODCOST EMPBOSS INVNUM INVQTY INVPRICE
------------------------------------------------
$1,090 301 310 2 $1,305
$1,090 401 380 1 $1,325
$190 401 400 3 $230
$615 201 480 4 $735
$615 401 510 2 $740
$615 301 570 3 $740
Intermediate result T3
PRODCOST MANAGER INVNUM INVQTY INVPRICE
------------------------------------------------
$1,090 Sally 310 2 $1,305
$1,090 Chuck 380 1 $1,325
$190 Chuck 400 3 $230
$615 Betty 480 4 $735
$615 Chuck 510 2 $740
$615 Sally 570 3 $740
Final result
MANAGER NUMSALES TOTSALES TOTCOST TOTMARG PCTMARG
----------------------------------------------------------
Betty 1 2940 2460 480 0.163265
Chuck 3 3495 2890 605 0.173104
Sally 2 4830 4025 805 0.166667
SQL and indexes
Before indexes are defined
MANAGER NUMSALES TOTSALES TOTCOST TOTMARG PCTMARG
----------------------------------------------------------
Betty 1 2940 2460 480 0.163265
Chuck 3 3495 2890 605 0.173104
Sally 2 4830 4025 805 0.166667
After indexes are defined
MANAGER NUMSALES TOTSALES TOTCOST TOTMARG PCTMARG
----------------------------------------------------------
Betty 1 2940 2460 480 0.163265
Chuck 3 3495 2890 605 0.173104
Sally 2 4830 4025 805 0.166667
Before index created
--------
2000
After index created
--------
2000
SQL and outer joins
Beach Land store sales
Invoice
PRODNAME INVQTY INVPRICE PRODLIST Amount
-------------------------------------------------
flippers 15 $19 $20 $285
jet ski . . $2,675 .
kayak . . $240 .
raft 20 $7 $7 $140
snorkel 20 $14 $15 $280
surfboard . . $750 .
windsurfer . . $1,325 .
X J1 J2 J3 J4
------------------------------------------------
2 j2_2
3 j3_3
0 j1_0 j2_0 j3_0 j4_0
1 j1_1
4 j4_4
X J1 J2 J3 J4
------------------------------------------------
0 j1_0 j2_0 j3_0 j4_0
1 j1_1
2 j2_2
3 j3_3
4 j4_4
Equivalent SQL queries
Query one
CUSTNAME
----------
Coast Shop
Surf Mart
Query two
CUSTNAME
--------------------
Coast Shop 4
Surf Mart 4
Query three
CUSTNAME NUMSTORE
--------------------
Coast Shop 4
Surf Mart 4
SQL and SAS macro language
ERROR: Naish not inserted for subject - sailing.
There are 3 referees already.
NAME SUBJECT
--------------------------------
Conner sailing
Fay sailing
Smythe sailing
This example demonstrates advanced features of the SQL procedure.
| Type: | Sample |
| Topic: | SAS Reference ==> Macro SAS Reference ==> Procedures ==> SQL |
| Date Modified: | 2005-08-24 16:06:31 |
| Date Created: | 2005-06-28 09:23:26 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | All | n/a | n/a |
