Sample 25449: Demonstrates basic SQL JOIN features
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.
This sample is from the SAS Sample Library. For additional information refer to SAS Help and Online Documentation.
/****************************************************************/
/* S A S S A M P L E L I B R A R Y */
/* */
/* NAME: SQLJOIN */
/* TITLE: Demonstrates Basic SQL JOIN Features */
/* PRODUCT: BASE */
/* SYSTEM: ALL */
/* KEYS: SQL DATMAN FULL JOIN WHERE SELECT COALESCE BETWEEN */
/* ORDER BY */
/* PROCS: SQL */
/* DATA: */
/* SUPPORT: UPDATE: */
/* REF: */
/* MISC: */
/* */
/****************************************************************/
/* The first step is to create the Paper, Roundt and Section */
/* tables which will be used in the following queries. */
data paper;
input author$1-8 section$9-16 title$17-43 @45 time time5.
duration;
format time time5.;
label title='Paper Title';
cards;
Tom Testing Automated Product Testing 9:00 35
Jerry Testing Involving Users 9:50 30
Nick Testing Plan to test, test to plan 10:30 20
Peter Info SysArtificial Intelligence 9:30 45
Paul Info SysQuery Languages 10:30 40
Lewis Info SysQuery Optimisers 15:30 25
Jonas Users Starting a Local User Group 14:30 35
Jim Users Keeping power users happy 15:15 20
Janet Users Keeping everyone informed 15:45 30
Marti GraphicsMulti-dimensional graphics 16:30 35
Marge GraphicsMake your own point! 15:10 35
Mike GraphicsMaking do without color 15:50 15
Jane GraphicsPrimary colors, use em! 16:15 25
;
data section;
input section$1-8 room$ convenor$;
cards;
Graphics Sable Denise
Info Sys Kudu Peter
Testing Sable Jenny
Users Kudu Sally
;
data roundt;
input leader$1-8 subject$9-30;
label subject='Roundtable Subject';
cards;
Mary External DBMS's
Nick Testing Networks
Jerry User Specifications
Peter Selling Solutions
Jim Distasteful Jokes
Marge Designing Fonts
;
proc sql;
/* What authors are also roundtable leaders? */
title2 'Papers and Roundtables';
select author, title, subject
from paper, roundt
where author = leader;
/* What papers and roundtable subjects will be discussed? */
/* The previous statement excludes those people that do not */
/* present a paper AS WELL AS lead a roundtable luncheon. */
title2 'Paper and Roundtable Discussions';
select author, title, subject
from paper full join roundt
on author = leader;
/* What papers and roundtable subjects will be discussed and */
/* who is responsible for presenting them? */
/* This is an improvement over the previous query as it */
/* combines the author/leader values into one column. */
title2 "What's being presented and who's responsible?";
select coalesce(author, leader) as person, title, subject
from paper full join roundt
on author = leader;
/* Whose papers are presented after this one ends? */
/* This demonstrates: */
/* 1) joining a table to another "instance" of itself */
/* 2) using a condition other than "=" in the join clause */
/* here were are interested in a range of matches. */
/* The aliases given to the two instances of the paper */
/* table are p (for previous) and n (for next) */
title2 'Papers to try next';
select p.author label='Just Heard',
n.author label='Then Try', n.title, n.section, n.time
from paper p, paper n
where n.time between p.time + p.duration*60
and p.time + (30+p.duration)*60
order by p.author;
/* Who is a very busy person and is giving a paper, leading */
/* a round table and chairing a section? */
/* This demonstrates a join involving more than two tables */
/* and using the DISTINCT keyword to eliminate duplicates. */
title2 'Very Busy Persons';
select distinct author
from paper, section, roundt
where author=convenor and author=leader;
/* A list is needed to post in the lobby that outlines the */
/* topics of papers and roundtable discussions and who is */
/* responsible for them. */
title2 'Composite Roster';
select coalesce(author,convenor,leader) as person,
title label='Gives Paper:',
section.section label='Convenes Section:',
subject label='Leads Roundtable on:'
from paper full join section on author=convenor
full join roundt on coalesce(author, convenor)=leader
order by 1;
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.
Papers and Roundtables
author Paper Title Roundtable Subject
-------------------------------------------------------------
Jerry Involving Users User Specifications
Nick Plan to test, test to plan Testing Networks
Peter Artificial Intelligence Selling Solutions
Jim Keeping power users happy Distasteful Jokes
Marge Make your own point! Designing Fonts
Paper and Roundtable Discussions
author Paper Title Roundtable Subject
-------------------------------------------------------------
Jane Primary colors, use em!
Janet Keeping everyone informed
Jerry Involving Users User Specifications
Jim Keeping power users happy Distasteful Jokes
Jonas Starting a Local User Group
Lewis Query Optimisers
Marge Make your own point! Designing Fonts
Marti Multi-dimensional graphics
External DBMS's
Mike Making do without color
Nick Plan to test, test to plan Testing Networks
Paul Query Languages
Peter Artificial Intelligence Selling Solutions
Tom Automated Product Testing
What's being presented and who's responsible?
person Paper Title Roundtable Subject
-------------------------------------------------------------
Jane Primary colors, use em!
Janet Keeping everyone informed
Jerry Involving Users User Specifications
Jim Keeping power users happy Distasteful Jokes
Jonas Starting a Local User Group
Lewis Query Optimisers
Marge Make your own point! Designing Fonts
Marti Multi-dimensional graphics
Mary External DBMS's
Mike Making do without color
Nick Plan to test, test to plan Testing Networks
Paul Query Languages
Peter Artificial Intelligence Selling Solutions
Tom Automated Product Testing
Papers to try next
Just
Heard Then Try Paper Title section time
----------------------------------------------------------------
Janet Marti Multi-dimensional graphics Graphics 16:30
Janet Jane Primary colors, use em! Graphics 16:15
Jerry Paul Query Languages Info Sys 10:30
Jerry Nick Plan to test, test to plan Testing 10:30
Jim Mike Making do without color Graphics 15:50
Jim Janet Keeping everyone informed Users 15:45
Jonas Marge Make your own point! Graphics 15:10
Jonas Lewis Query Optimisers Info Sys 15:30
Jonas Jim Keeping power users happy Users 15:15
Lewis Jane Primary colors, use em! Graphics 16:15
Marge Mike Making do without color Graphics 15:50
Marge Janet Keeping everyone informed Users 15:45
Marge Jane Primary colors, use em! Graphics 16:15
Mike Jane Primary colors, use em! Graphics 16:15
Mike Marti Multi-dimensional graphics Graphics 16:30
Peter Paul Query Languages Info Sys 10:30
Peter Nick Plan to test, test to plan Testing 10:30
Tom Jerry Involving Users Testing 9:50
Very Busy Persons
author
--------
Peter
Composite Roster
Convenes
person Gives Paper: Section: Leads Roundtable on:
-----------------------------------------------------------------------
Denise Graphics
Jane Primary colors, use em!
Janet Keeping everyone informed
Jenny Testing
Jerry Involving Users User Specifications
Jim Keeping power users happy Distasteful Jokes
Jonas Starting a Local User Group
Lewis Query Optimisers
Marge Make your own point! Designing Fonts
Marti Multi-dimensional graphics
Mary External DBMS's
Mike Making do without color
Nick Plan to test, test to plan Testing Networks
Paul Query Languages
Peter Artificial Intelligence Info Sys Selling Solutions
Sally Users
Tom Automated Product Testing
This example demonstrates basic SQL JOIN features.
| Type: | Sample |
| Topic: | SAS Reference ==> Procedures ==> SQL
|
| Date Modified: | 2005-08-24 16:06:34 |
| Date Created: | 2005-05-23 13:54:32 |
Operating System and Release Information
| SAS System | Base SAS | All | n/a | n/a |
