innovationtip

Main Menu

Share innovation with the world

innovationtip / Base SAS / PROC SQL: Using SAS

PROC SQL: Using SAS

INSTRUCTIONS FOR CREATING TABLES AND USING CODE EXAMPLES

  1. Create the tables using the DATA steps below by copying and pasting    the code into the SAS Display Manager. Each DATA step consists of complete code and instream data.
  2. Copy and paste the PROC SQL code into the SAS Display Manager.
  3. Run the code corresponding to the selected example.

READ IN THE SAMPLE DATA

The following DATA step code is set up to store all files and tables in the WORK directory. If you wish to use a different directory, define a user-defined libref with a LIBNAME statement, store or copy the data to the desired folder, then reference the libref in the FROM clauses used in the examples. 

options ls=132 nofmterr nocenter nodate nonumber;

/**********************/
/*
CUSTOMERS TABLE
*/
/**********************/

DATA CUSTOMERS;
INFILE CARDS MISSOVER;
INPUT @1 CUSTNUM 4. @7 CUSTNAME $25. @36 CUSTCITY $20.;
CARDS;
101 La Mesa Computer Land La Mesa
201 Vista Tech Center Vista
301 Coronado Internet Zone Coronado
401 La Jolla Computing La Jolla
501 Alpine Technical Center Alpine
601 Oceanside Computer Land Oceanside
701 San Diego Byte Store San Diego
801 Jamul Hardware & Software Jamul
901 Del Mar Tech Center Del Mar
1001 Lakeside Software Center Lakeside
1101 Bonsall Network Store Bonsall
1201 Rancho Santa Fe Tech Rancho Santa Fe
1301 Spring Valley Byte Center Spring Valley
1401 Poway Central Poway
1501 Valley Center Tech Center Valley Center
1601 Fairbanks Tech USA Fairbanks Ranch
1701 Blossom Valley Tech Blossom Valley
1801 Chula Vista Networks
;
RUN;

/**********************/
/*
CUSTOMERS2 TABLE
*/
/**********************/

DATA CUSTOMERS2;
INFILE CARDS MISSOVER;
INPUT @1 CUSTNUM 2. @5 CUSTNAME $10. @17 CUSTCITY $20.;
CARDS;
1 Smith San Diego
7 Lafler Spring Valley
11 Jones Carmel
13 Thompson Miami
7 Loffler Spring Valley
1 Smithe San Diego
7 Laughler Spring Valley
7 Laffler Spring Valley
;
RUN;

/**********************/
/*
INVENTORY TABLE
*/
/**********************/

DATA INVENTORY;
INFILE CARDS MISSOVER;
INPUT @1 PRODNUM 4. @8 INVENQTY 2. @13 ORDDATE MMDDYY10. @27 INVENCST COMMA10.2 @39 MANUNUM
3.;
FORMAT INVENCST DOLLAR10.2 ORDDATE MMDDYY10.;
CARDS;
1110 20 09/01/2000 45,000.00 111
1700 10 08/15/2000 28,000.00 170
5001 5 08/15/2000 1,000.00 500
5002 3 08/15/2000 900.00 500
5003 10 08/15/2000 2,000.00 500
5004 20 09/01/2000 1,400.00 500
5001 2 09/01/2000 1,200.00 600;
RUN;

/**********************/
/*
  INVOICE TABLE
   */
/**********************/
DATA INVOICE;
INFILE CARDS MISSOVER;
INPUT @1 INVNUM 4. @7 MANUNUM 3. @13 CUSTNUM 4. @20 INVQTY 2. @25 INVPRICE COMMA10.2 @37
PRODNUM 4.;
FORMAT INVPRICE DOLLAR12.2;
CARDS;

1001 500 201 5 1,495.00 5001
1002 600 1301 2 1,598.00 6001
1003 210 101 7 245.00 2101
1004 111 501 3 9,600.00 1110
1005 500 801 2 798.00 5002
1006 500 901 4 396.00 6000
1007 500 401 7 23,100.00 1200;

RUN;

/****************************/
/*
MANUFACTURERS TABLE
*/
/****************************/

DATA MANUFACTURERS;
INFILE CARDS MISSOVER;
INPUT @1 MANUNUM 3. @6 MANUNAME $22. @29 MANUCITY $12. @41 MANUSTAT $2.;
CARDS;
111 Cupid Computer Houston TX
210 Global Comm Corp San Diego CA
600 World Internet Corp Miami FL
120 Storage Devices Inc San Mateo CA
500 KPL Enterprises San Diego CA
700 San Diego PC Planet San Diego CA;
RUN;

/***********************/
/*
PRODUCTS TABLE  
*/
/***********************/

DATA PRODUCTS;
INFILE CARDS MISSOVER;
INPUT @1 PRODNUM 4. @7 PRODNAME $25. @33 MANUNUM 3. @38 PRODTYPE $15. @53 PRODCOST COMMA10.2;
FORMAT PRODCOST DOLLAR9.2;
CARDS;
1110 Dream Machine 111 Workstation 3,200.00
1200 Business Machine 120 Workstation 3,300.00
1700 Travel Laptop 170 Laptop 3,400.00
2101 Analog Cell Phone 210 Phone 35.00
2102 Digital Cell Phone 210 Phone 175.00
2200 Office Phone 220 Phone 130.00
5001 Spreadsheet Software 500 Software 299.00
5002 Database
Software 500 Software 399.00
5003 Wordprocessor Software 500 Software 299.00
5004 Graphics Software 500 Software 299.00;
RUN;

/***********************/
/*
PURCHASES TABLE
*/
/***********************/
DATA PURCHASES;
INFILE CARDS MISSOVER;
INPUT @1 CUSTNUM 2. @5 ITEM $10. @18 UNITS 2. @21 UNITCOST COMMA12.2;
FORMAT UNITCOST DOLLAR12.2;
CARDS;
1 Chair 1 179.00
1 Pens 12 0.89
1 Paper 4 6.95
1 Stapler 1 8.95
7 Mouse Pad 1 11.79
7 Pens 24 1.59
13 Markers . 0.99;
RUN;

/**********************************************************/
/*
Chapter 2: Working with Data in PROC SQL
*/
/**********************************************************/
/*
2.2.1 Code Example: Numeric Data
*/
PROC
SQL;
CREATE TABLE
PURCHASES
(CUSTNUM CHAR
(4),
ITEM CHAR
(10)
UNITS NUM
UNITCOST NUM(8,2));
QUIT;

/* 2.2.1 Code Example: Numeric Data */
DATA PURCHASES;
LENGTH CUSTNUM $4.
ITEM $10.
UNITS 3.
UNITCOST 4.;
LABEL CUSTNUM = ‘Customer
Number’
ITEM = ‘Item
Purchased’
UNITS = ‘# Units
Purchased’
UNITCOST = ‘Unit Cost’
;
FORMAT UNITCOST DOLLAR12.2;
RUN;
PROC
CONTENTS DATA=PURCHASES;
RUN;

/* 2.2.5 Code Example: Arithmetic and Missing Data */
PROC
SQL;
SELECT
CUSTNUM,
ITEM,
UNITS,
UNITCOST,
UNITS * UNITCOST AS
TOTAL
FROM
PURCHASES
ORDER BY
TOTAL;
QUIT;

/* 2.2.6.1 Code Example: Creating Column Aliases */
PROC
SQL;
SELECT
PRODNAME,
PRODTYPE,
PRODCOST * 0.80 AS
Discount_Price
FROM
PRODUCTS
ORDER BY
3;
QUIT;

/* 2.2.6.2 Code Example: Finding Duplicate Values */
PROC
SQL;
SELECT DISTINCT
MANUNUM
FROM
INVENTORY;
QUIT;

/* 2.2.6.3 Code Example: Finding Unique Values */
PROC
SQL;
SELECT UNIQUE
MANUNUM
FROM
INVENTORY;
QUIT;

/* 2.3.1 Code Example: Comparison Operators */
PROC
SQL;
SELECT
PRODNAME,
PRODTYPE,
PRODCOST
FROM
PRODUCTS
WHERE
PRODCOST > 300;
QUIT;

/* 2.3.2 Code Example: AND Logical Operator */
PROC
SQL;
SELECT
  PRODNAME,
PRODTYPE,
PRODCOST
FROM
PRODUCTS
WHERE PRODTYPE = ‘Software’
AND
PRODCOST > 300;
QUIT;

/* 2.3.2 Code Example: OR Logical Operator */
PROC
SQL;
SELECT
PRODNAME,
PRODTYPE,
PRODCOST
FROM
  PRODUCTS
WHERE PRODTYPE = ‘Software’
OR
PRODCOST > 300;
QUIT;

/* 2.3.2 Code Example: NOT Logical Operator */
PROC
SQL;
SELECT
PRODNAME,
PRODTYPE,
PRODCOST
FROM
  PRODUCTS
WHERE NOT PRODTYPE = ‘Software’
AND
NOT
PRODCOST > 300;
QUIT;

/* 2.3.3 Code Example: Arithmetic Operator */
PROC
SQL;
SELECT
PRODNAME,
PRODTYPE,
PRODCOST * 0.80
FROM
PRODUCTS;
QUIT;
/* 2.3.3 Code Example: CALCULATED Keyword
*/
PROC
SQL;
SELECT
PRODNAME,
PRODTYPE,
PRODCOST * 0.80 AS
DISCOUNT PRICE
FORMAT=DOLLAR9.2,
PRODCOST – CALCULATED DISCOUNT_PRICE AS
LOSS
FORMAT=DOLLAR7.2
FROM
PRODUCTS
ORDER BY
3;
QUIT;

/* 2.3.4.1 Code Example: Concatenating Strings Together */
PROC
SQL;
SELECT MANUCITY || “,”
|| MANUSTAT
FROM
MANUFACTURERS;
QUIT;

/* 2.3.4.2 Code Example: Finding the Length of a String */
PROC
SQL;
SELECT
PRODNUM,
PRODNAME,
LENGTH(PRODNAME) AS
Length
FROM
PRODUCTS;
QUIT;

/* 2.3.4.3 Code Example: Combining Functions and Operators #1 */
PROC
SQL;
UPDATE
PRODUCTS
SET
PRODNAME = SCAN(PRODNAME,1) || TRIM(PRODTYPE);
QUIT;

/* 2.3.4.3 Code Example: Combining Functions and Operators #2 */
PROC
SQL;
UPDATE
PRODUCTS
SET
PRODNAME = SCAN(PRODNAME,1) || TRIM(PRODTYPE)
WHERE PRODTYPE IN (‘Phone’
);
QUIT;

/* 2.3.4.4 Code Example: LEFT Aligning Characters */
PROC
SQL;
SELECT LEFT(TRIM(MANUCITY) || “,
|| MANUSTAT)
FROM
MANUFACTURERS;
QUIT;

/* 2.3.4.4 Code Example: RIGHT Aligning Characters */
PROC
SQL;
SELECT RIGHT
(MANUCITY)
FROM
MANUFACTURERS;
QUIT;

/* 2.3.4.5 Code Example: Finding the Occurrence of a Pattern with INDEX */
PROC
SQL;
SELECT
PRODNUM,
PRODNAME,
PRODTYPE
FROM
PRODUCTS
WHERE INDEX(PRODNAME, ‘phone’
) > 0;
QUIT;

/* 2.3.4.7 Code Example: Changing the Case in a String UPCASE */
PROC
SQL;
SELECT
PRODNUM,
PRODNAME,
PRODTYPE
FROM
PRODUCTS
WHERE INDEX(UPCASE(PRODNAME), ‘PHONE’
) > 0;
QUIT;

/* 2.3.4.7 Code Example: Changing the Case in a String LOWCASE */
PROC
SQL;
SELECT
PRODNUM,
PRODNAME,
PRODTYPE
FROM
PRODUCTS
WHERE INDEX(LOWCASE(PRODNAME), phone’
) > 0;
QUIT;

/* 2.3.4.8 Code Example: Extracting Information from a String */
PROC
SQL;
SELECT
PRODNUM,
PRODNAME,
PRODTYPE,
SUBSTR(PRODTYPE,1,4)
FROM
PRODUCTS
WHERE
PRODCOST > 100.00;
QUIT;

/* 2.3.4.9 Code Example: Phonetic Matching #1 */
PROC
SQL;
SELECT
CUSTNUM,
CUSTNAME,
CUSTCITY
FROM
CUSTOMERS2
WHERE CUSTNAME =* ‘Lafler’
;
QUIT;

/* 2.3.4.9 Code Example: Phonetic Matching #2 */
PROC
SQL;
SELECT
CUSTNUM,
CUSTNAME,
CUSTCITY
FROM
CUSTOMERS2
WHERE CUSTNAME =* ‘Lafler’
OR
CUSTNAME =* ‘Laughler’
OR
CUSTNAME =* ‘Lasler’
;
QUIT;

/* 2.3.4.10 Code Example: Finding the First Non-Missing Value */
PROC
SQL;
SELECT
CUSTNUM,
ITEM,
UNITS,
UNITCOST,
(COALESCE(UNITS, 0) * COALESCE
(UNITCOST, 0))
AS
Totcost FORMAT=DOLLAR6.2
FROM
PURCHASES;
QUIT;

/* 2.3.4.11 Code Example: Producing a Row Number with the MONOTONIC() Function */
PROC
SQL;
SELECT MONOTONIC() AS
Row_Number FORMAT=COMMA6.,
ITEM,
UNITS,
UNITCOST
FROM
PURCHASES;
QUIT;

/* 2.3.4.11 Code Example: Producing a Row Number with the NUMBER Option */
PROC
SQL NUMBER;
SELECT
ITEM,
UNITS,
UNITCOST
FROM
PURCHASES;
QUIT;

/* 2.3.5 Code Example: Summarizing Data with the COUNT(*) Function */
PROC
SQL;
SELECT COUNT(*) AS
Row_Count
FROM
PURCHASES;
QUIT;

/* 2.3.5 Code Example: Summarizing Data with the COUNT Function */
PROC
SQL;
SELECT COUNT(UNITS) AS
Non_Missing_Row_Count
FROM
PURCHASES;
QUIT;

/* 2.3.5 Code Example: Summarizing Data with the MIN Function */
PROC
SQL;
SELECT MIN(prodcost) AS
Cheapest
Format=dollar9.2 Label=‘Least
Expensive’
FROM
PRODUCTS;
QUIT;

/* 2.3.5 Code Example: Summarizing Data with the SUM Function */
PROC
SQL;
SELECT SUM
((UNITS) * (UNITCOST))
AS
Total_Purchases FORMAT=DOLLAR6.2
FROM
PURCHASES
WHERE UPCASE(ITEM)=‘PENS’
OR
UPCASE(ITEM)=‘MARKERS’
;
QUIT;

/* 2.3.5 Code Example: Summarizing Data Down Rows */
PROC
SQL;
SELECT AVG(PRODCOST) AS
Average_Product_Cost
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE)
IN (“SOFTWARE”);
QUIT;

/* 2.3.5 Code Example: Summarizing Data Across Columns */
PROC
SQL;
SELECT
PRODNUM,
(INVPRICE / INVQTY) AS
Averge_Price FORMAT=DOLLAR8.2
FROM
INVOICE;
QUIT;

/* 2.3.6.1 Code Example: Selecting a Range of Values #1 */
PROC
SQL;
SELECT
PRODNAME,
PRODTYPE,
PRODCOST
FROM
PRODUCTS
WHERE PRODCOST BETWEEN 200 AND
500;
QUIT;

/* 2.3.6.1 Code Example: Selecting a Range of Values #2 */
PROC
SQL;
SELECT
PRODNUM,
INVENQTY,
ORDDATE
FROM
INVENTORY
WHERE YEAR(ORDDATE) BETWEEN 1999 AND
2000;
QUIT;

/* 2.3.6.1 Code Example: Selecting a Range of Values #3 */
PROC
SQL;
SELECT
PRODNUM,
INVENQTY,
ORDDATE
FROM
INVENTORY
WHERE (YEAR(ORDDATE) BETWEEN 1999 AND 2000)
OR
INVENQTY > 15;
QUIT;

/* 2.3.6.2 Code Example: Selecting Non-consecutive Values #1 */
PROC
SQL;
SELECT
PRODNAME,
PRODTYPE,
PRODCOST
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) = ‘PHONE’
;
QUIT;

/* 2.3.6.2 Code Example: Selecting Non-consecutive Values #2 */
PROC
SQL;
SELECT
PRODNAME,
PRODTYPE,
PRODCOST
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) IN (‘PHONE’, ‘SOFTWARE’
);
QUIT;

/* 2.3.6.3 Code Example: Testing for NULL or Missing Values #1 */
PROC
SQL;
SELECT
PRODNUM,
INVENQTY,
INVENCST
FROM
INVENTORY
WHERE INVENQTY IS NULL
;
QUIT;

/* 2.3.6.3 Code Example: Testing for NULL or Missing Values #2 */
PROC
SQL;
SELECT
PRODNUM,
INVENQTY,
INVENCST
FROM
INVENTORY
WHERE INVENQTY IS NOT NULL
;
QUIT;

/* 2.3.6.3 Code Example: Testing for NULL or Missing Values #3 */
PROC
SQL;
SELECT
PRODNUM,
INVENQTY,
INVENCST
FROM
INVENTORY
WHERE INVENQTY IS NOT
MISSING;
QUIT;

/* 2.3.6.4 Code Example: Finding Patterns in a String #1 */
PROC
SQL;
SELECT
PRODNAME
FROM
PRODUCTS
WHERE PRODNAME LIKE ‘A%’
;
QUIT;

/* 2.3.6.4 Code Example: Finding Patterns in a String #2 */
PROC
SQL;
SELECT
PRODNAME,
PRODTYPE,
PRODCOST
FROM
PRODUCTS
WHERE PRODTYPE LIKE ‘%Soft%’
;
QUIT;

/* 2.3.6.4 Code Example: Finding Patterns in a String #3 */
PROC
SQL;
SELECT
PRODNAME
FROM
PRODUCTS
WHERE PRODNAME LIKE ‘%
;
QUIT;

/* 2.3.6.4 Code Example: Finding Patterns in a String #4 */
PROC
SQL;
SELECT
PRODNAME,
PRODTYPE,
PRODCOST
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) LIKE ‘P_____’
;
QUIT;

/* 2.3.6.4 Code Example: Finding Patterns in a String #5 */
PROC
SQL;
SELECT
PRODNAME
FROM
PRODUCTS
WHERE PRODNAME LIKE ‘___a%’
;
QUIT;

/* 2.3.6.5 Code Example: Testing for the Existence of a Value */
PROC
SQL;
SELECT
CUSTNUM,
CUSTNAME,
CUSTCITY
FROM
CUSTOMERS2 C
WHERE
EXISTS
(SELECT
*
FROM
PURCHASES P
WHERE
C.CUSTNUM = P.CUSTNUM);
QUIT;

/* 2.4.1 Code Example: Displaying Dictionary Table Definitions */
PROC
SQL;
DESCRIBE
TABLE
DICTIONARY.OPTIONS
;
QUIT;

/* 2.4.3.1 Code Example: Dictionary.CATALOGS */
PROC
SQL;
SELECT
*
FROM
DICTIONARY.CATALOGS
WHERE LIBNAME=“SASUSER”
;
QUIT;

/* 2.4.3.2 Code Example: Dictionary.COLUMNS */
PROC
SQL;
SELECT
*
FROM
DICTIONARY.COLUMNS
WHERE UPCASE(LIBNAME)=“WORK”
AND
UPCASE(NAME)=“CUSTNUM”
;
QUIT;

/* 2.4.3.3 Code Example: Dictionary.EXTFILES */
PROC
SQL;
SELECT
*
FROM
DICTIONARY.EXTFILES;
QUIT;

/* 2.4.3.4 Code Example: Dictionary.INDEXES */
PROC
SQL;
SELECT
*
FROM
DICTIONARY.INDEXES
WHERE UPCASE(NAME)=“CUSTNUM” /* Column Name
*/
AND UPCASE(LIBNAME)=“WORK”; /* Library Name
*/
QUIT;

/* 2.4.3.5 Code Example: Dictionary.MACROS */
PROC
SQL;
SELECT
*
FROM
DICTIONARY.MACROS
WHERE UPCASE(SCOPE)=“GLOBAL”
;
QUIT;

/* 2.4.3.6 Code Example: Dictionary.MEMBERS */
PROC
SQL;
SELECT
*
FROM
DICTIONARY.MEMBERS
WHERE UPCASE(LIBNAME)=“WORK”
;
QUIT;

/* 2.4.3.7 Code Example: Dictionary.OPTIONS */
PROC
SQL;
SELECT
*
FROM DICTIONARY.OPTIONS
;
QUIT;

/* 2.4.3.8 Code Example: Dictionary.TABLES */
PROC
SQL;
SELECT
*
FROM
DICTIONARY.TABLES
WHERE UPCASE(LIBNAME)=“WORK”
;
QUIT;

/* 2.4.3.9 Code Example: Dictionary.TITLES */
PROC
SQL;
SELECT
*
FROM
DICTIONARY.TITLES;
QUIT;

/* 2.4.3.10 Code Example: Dictionary.VIEWS */
PROC
SQL;
SELECT
*
FROM
DICTIONARY.VIEWS
WHERE UPCASE(LIBNAME)=“WORK”
;
QUIT;

/**********************************************************/
/*
Chapter 3: Formatting Output
*/
/**********************************************************/
/*
3.2.1 Code Example: Writing a Blank Line Between Each Row
*/
PROC SQL DOUBLE
;
SELECT
*
FROM
INVOICE;
QUIT;

/* 3.2.1 Code Example: Resetting to Single-spaced Output */
PROC
SQL;
RESET NODOUBLE;
QUIT;

/* 3.2.2 Code Example: Displaying Row Numbers */
PROC
SQL NUMBER;
SELECT
ITEM,
UNITS,
UNITCOST
FROM
PURCHASES;
QUIT;

/* 3.2.3 Code Example: Concatenating Character Strings #1 */
PROC
SQL;
SELECT
manucity || manustat
FROM
MANUFACTURERS;
QUIT;

/* 3.2.3 Code Example: Concatenating Character Strings #2 */
PROC
SQL;
SELECT TRIM(manucity) || manustat AS
Headquarters
FROM
MANUFACTURERS;
QUIT;

/* 3.2.4 Code Example: Inserting Text and Constraints Between Columns #1 */
PROC
SQL;
SELECT trim(manucity) || ‘,
|| manustat
As
Headquarters
FROM
MANUFACTURERS;
QUIT;

/* 3.2.4 Code Example: Inserting Text and Constraints Between Columns #2 */
PROC
SQL;
SELECT CATX(‘,’
, manucity, manustat)
As
Headquarters
FROM
MANUFACTURERS;
QUIT;

/* 3.2.5 Code Example: Using Scalar Expressions with Selected Columns #1 */
PROC
SQL;
SELECT
prodname,
prodcost,
.075 * prodcost
FROM
PRODUCTS;
QUIT;

/* 3.2.5 Code Example: Using Scalar Expressions with Selected Columns #2 */
PROC
SQL;
SELECT
prodname,
prodcost,
.075 * prodcost AS
Sales_Tax
FROM
PRODUCTS
ORDER BY
3;
QUIT;

/* 3.2.5 Code Example: Using Scalar Expressions with Selected Columns #3 */
PROC
SQL;
SELECT
prodname,
prodcost,
.075 * prodcost FORMAT=DOLLAR7.2
LABEL=‘Sales
Tax’
FROM
PRODUCTS;
QUIT;

/* 3.2.6 Code Example: Ordering Output by Columns #1 */
PROC
SQL;
SELECT
*
FROM
PRODUCTS
ORDER BY
prodnum;
QUIT;

/* 3.2.6 Code Example: Ordering Output by Columns #2 */
PROC
SQL;
SELECT
prodname, prodtype, prodcost, prodnum
FROM
PRODUCTS
ORDER BY prodtype, DESC
prodcost;
QUIT;

/* 3.2.7 Code Example: Grouping Data with Summary Functions #1 */
PROC
SQL;
SELECT
prodtype,
prodcost
FROM
PRODUCTS
GROUP BY
prodtype;
QUIT;

/* 3.2.7 Code Example: Grouping Data with Summary Functions #2 */
PROC
SQL;
SELECT
prodtype,
MIN(prodcost) AS
Cheapest
Format=dollar9.2 Label=‘Least
Expensive’
FROM
PRODUCTS
GROUP BY
prodtype;
QUIT;

/* 3.2.8 Code Example: Grouping Data and Sorting */
PROC
SQL;
SELECT
prodtype,
MIN(prodcost) AS
Cheapest
Format=dollar9.2 Label=‘Least
Expensive’
FROM
PRODUCTS
GROUP BY
prodtype
ORDER BY
cheapest;
QUIT;

/* 3.2.9 Code Example: Subsetting Groups with the HAVING Clause */
PROC
SQL;
SELECT
prodtype,
AVG
(prodcost)
FORMAT=DOLLAR9.2 LABEL=‘Average Product
Cost’
FROM
PRODUCTS
GROUP BY
prodtype
HAVING AVG
(prodcost) <= 200.00;
QUIT;

/* 3.3.2 Code Example: Sending Output to a SAS Data Set */
ODS LISTING CLOSE
;
ODS OUTPUT SQL_Results = SQL_DATA;
PROC
SQL;
TITLE1 ‘Delivering Output to a Data Set’
;
SELECT
prodname, prodtype, prodcost, prodnum
FROM
PRODUCTS
ORDER BY
prodtype;
QUIT;
ODS OUTPUT CLOSE
;
ODS LISTING;

/* 3.3.3 Code Example: Converting Output to Rich Text Format */
ODS LISTING CLOSE
;
ODS RTF FILE=‘c:\SQL_Results.rtf’
;
PROC
SQL;
TITLE1 ‘Delivering Output to Rich Text Format’
;
SELECT
prodname, prodtype, prodcost, prodnum
FROM
PRODUCTS
ORDER BY
prodtype;
QUIT;
ODS RTF CLOSE
;
ODS LISTING;

/* 3.3.4 Code Example: Delivering Results to the Web */
ODS LISTING CLOSE
;
ODS HTML BODY=
‘c:\Products-body.html’
CONTENTS=
‘c:\Products-contents.html’
PAGE=
‘c:\Products-page.html’
FRAME=‘c:\Products-frame.html’
;
PROC
SQL;
TITLE1 ‘Products List’
;
SELECT
prodname, prodtype, prodcost, prodnum
FROM
PRODUCTS
ORDER BY
prodtype;
QUIT;
ODS HTML CLOSE
;
ODS LISTING;

/**********************************************************/
/*
Chapter 4: Coding PROC SQL Logic
*/
/**********************************************************/
/*
4.2 Code Example: Conditional Logic #1
*/
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE
PRODCOST < 400.00;
QUIT;

/* 4.2 Code Example: Conditional Logic #2 */
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE
PRODCOST > 400.00;
QUIT;

/* 4.2 Code Example: Conditional Logic #3 */
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE PRODTYPE = “Software”
;
QUIT;

/* 4.2 Code Example: Conditional Logic #4 */
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) = “SOFTWARE”
;
QUIT;

/* 4.3 Code Example: CASE Expressions #1 */
PROC
SQL;
SELECT
MANUNAME,
MANUSTAT,
CASE
WHEN MANUSTAT = ‘CA’ THEN
‘West’
WHEN MANUSTAT = ‘FL’ THEN
‘East’
WHEN MANUSTAT = ‘TX’ THEN
‘Central’
ELSE
‘Unknown’
END AS
Region
FROM
MANUFACTURERS;
QUIT;

/* 4.3 Code Example: CASE Expressions #2 */
PROC
SQL;
SELECT
PRODNAME,
CASE
PRODTYPE
WHEN ‘Laptop’ THEN
‘Hardware’
WHEN ‘Phone’ THEN
‘Hardware’
WHEN ‘Software’ THEN
‘Software’
WHEN ‘Workstation’ THEN
‘Hardware’
ELSE
‘Unknown’
END AS
Product_Classification
FROM
PRODUCTS;
QUIT;

/* 4.3.1 Code Example: Case Logic versus COALESCE Expression #1 */
PROC
SQL;
SELECT
CUSTNAME,
CASE
WHEN CUSTCTY IS NOT NULL THEN
CUSTCITY
ELSE
‘Unknown’
END AS
Customer_City
FROM
CUSTOMER;
QUIT;

/* 4.3.1 Code Example: Case Logic versus COALESCE Expression #2 */
PROC
SQL;
SELECT
CUSTNAME,
COALESCE(CUSTCTY,‘Unknown’
)
AS
Customer_City
FROM
CUSTOMER;
QUIT;

/* 4.3.1 Code Example: Case Logic versus COALESCE Expression #3 */
PROC
SQL;
SELECT
ITEM,
COALESCE
(UNITS, 0)
FROM
PURCHASES;
QUIT;

/* 4.3.2 Code Example: Assigning Labels and Grouping Data #1 */
PROC
FORMAT;
VALUE INVQTY
0 – 5 = ‘Low on Stock
Reorder’
6 – 10 = ‘Stock Levels
OK’
11 – 99 = ‘Plenty of
Stock’
100 – 999 = ‘Excessive Quantities’
;
RUN;

PROC SORT DATA=INVENTORY;
BY
INVENQTY;
RUN;

PROC PRINT DATA=INVENTORY(KEEP=PRODNUM INVENQTY) NOOBS;
FORMAT INVENQTY INVQTY.;
RUN;

/* 4.3.2 Code Example: Assigning Labels and Grouping Data #2 */
PROC
SQL;
SELECT
PRODNUM,
CASE
WHEN
INVENQTY LE 5
THEN ‘Low on Stock
Reorder’
WHEN
6 LE INVENQTY LE 10
THEN ‘Stock Levels
OK’
WHEN
11 LE INVENQTY LE 99
THEN ‘Plenty of
Stock’
ELSE ‘Excessive
Quantities’
END AS
Inventory_Status
FROM
INVENTORY
ORDER BY
INVENQTY;
QUIT;

/* 4.3.3 Code Example: Logic and Nulls #1 */
PROC
SQL;
SELECT
MANUNAME,
MANUSTAT,
CASE
WHEN MANUSTAT = ‘CA’ THEN ‘South
West’
WHEN MANUSTAT = ‘FL’ THEN ‘South
East’
WHEN MANUSTAT = ‘TX’ THEN
‘Central’
WHEN MANUSTAT = THEN
‘Missing’
ELSE
‘Unknown’
END AS
Region
FROM
MANUFACTURERS;
QUIT;

/* 4.3.3 Code Example: Logic and Nulls #2 */
PROC
SQL;
SELECT
MANUNAME,
MANUSTAT,
CASE
WHEN MANUSTAT = ‘CA’ THEN ‘South
West’
WHEN MANUSTAT = ‘FL’ THEN ‘South
East’
WHEN MANUSTAT = ‘TX’ THEN
‘Central’
WHEN MANUSTAT = THEN
‘Missing’
ELSE
‘Unknown’
END AS
Region
FROM
MANUFACTURERS;
QUIT;

/* 4.4.1.1 Code Example: Creating a Macro Variable with %LET #1 */
%LET PRODTYPE=SOFTWARE;
TITLE “Listing of &PRODTYPE Products”
;
PROC
SQL;
SELECT
PRODNAME,
PRODCOST
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) =
“&PRODTYPE”
ORDER BY
PRODCOST;
QUIT;

/* 4.4.1.1 Code Example: Creating a Macro Variable with %LET #2 */
%MACRO VIEW
(NAME);
%IF %UPCASE(&NAME) ^= %STR(PRODUCTS)
AND
%UPCASE(&NAME) ^= %STR(MANUFACTURERS)
AND
%UPCASE(&NAME) ^= %STR(INVENTORY) %THEN
%DO;
%PUT A valid view name was not supplied and no
output
will be generated!;
%END
;
%ELSE
%DO;
PROC
SQL;
TITLE “Listing of &NAME View”
;
%IF %UPCASE(&NAME)=%STR(PRODUCTS) %THEN
%DO;
SELECT
PRODNAME,
PRODCOST
FROM
&NAME._view
ORDER BY
PRODCOST;
%END
;
%ELSE %IF %UPCASE(&NAME)=%STR(MANUFACTURERS) %THEN
%DO;
SELECT
MANUNAME,
MANUCITY,
MANUSTAT
FROM
&NAME._view
ORDER BY
MANUCITY;
%END
;
%ELSE %IF %UPCASE(&NAME)=%STR(INVENTORY) %THEN
%DO;
SELECT
PRODNUM,
INVENQTY,
INVENCST
FROM &NAME.
view
ORDER
BY
INVENCST;
%END
;
QUIT;
%END
;
%MEND VIEW
;
%VIEW
(Products)

/* 4.4.1.2 Code Example: Creating a Macro Variable from a Table Row Column #1 */
PROC
SQL NOPRINT;
SELECT
PRODNAME,
PRODCOST
INTO
:PRODNAME,
:PRODCOST
FROM
PRODUCTS;
QUIT;
%PUT &PRODNAME &PRODCOST;

/* 4.4.1.2 Code Example: Creating a Macro Variable from a Table Row Column #2 */
PROC
SQL NOPRINT;
SELECT
PRODNAME,
PRODCOST
INTO
:PRODNAME,
:PRODCOST
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) IN (‘SOFTWARE’
);
QUIT;
%PUT &PRODNAME &PRODCOST;

/* 4.4.1.3 Code Example: Creating a Macro Variable with Aggregate Functions */
PROC
SQL NOPRINT;
SELECT MIN
(PRODCOST) FORMAT=DOLLAR10.2
INTO
:MIN_PRODCOST
FROM
PRODUCTS;
QUIT;
%PUT &MIN_PRODCOST;

/* 4.4.1.4 Code Example: Creating Multiple Macro Variables */
PROC
SQL NOPRINT;
SELECT
PRODNAME,
PRODCOST
INTO
:PRODUCT1 – :PRODUCT3,
:COST1 – :COST3
FROM
PRODUCTS
ORDER BY
PRODCOST;
QUIT;
%PUT &PRODUCT1 &COST1;
%PUT &PRODUCT2 &COST2;
%PUT &PRODUCT3 &COST3;

/* 4.4.1.5 Code Example: Creating a List of Values in a Macro Variable #1 */
PROC
SQL NOPRINT;
SELECT
MANUNAME
INTO :MANUNAME SEPARATED BY

FROM
MANUFACTURERS
WHERE UPCASE(MANUCITY)=‘SAN DIEGO’
;
QUIT;
%PUT &MANUNAME;

/* 4.4.1.5 Code Example: Creating a List of Values in a Macro Variable #2 */
PROC
SQL NOPRINT;
SELECT
MANUNAME
INTO :MANUNAME SEPARATED BY ‘,

FROM
MANUFACTURERS
WHERE UPCASE(MANUCITY)=‘SAN DIEGO’
;
QUIT;
%PUT &MANUNAME;

/* 4.4.1.6 Code Example: Using Automatic Macro Variables to Control Processing */
%PUT _AUTOMATIC_;

/* 4.4.2.2 Code Example: Cross-Referencing Columns */
/*
NOTE: This example has been Corrected with Double-quotes!
*/
%MACRO COLUMNS(LIB, COLNAME);
PROC
SQL;
SELECT
LIBNAME, MEMNAME, NAME, TYPE, LENGTH
FROM
DICTIONARY.COLUMNS
WHERE LIBNAME=“&LIB”
AND
UPCASE(NAME)=“&COLNAME”
AND
MEMTYPE=“DATA”
;
QUIT;
%MEND COLUMNS;
%COLUMNS(WORK
,CUSTNUM);

/* 4.4.2.3 Code Example: Determining the Number of Rows in a Table */
%MACRO NOBS(LIB, TABLE
);
PROC
SQL;
SELECT
LIBNAME, MEMNAME, NOBS
FROM
DICTIONARY.TABLES
WHERE UPCASE(LIBNAME)=“&LIB”
AND
UPCASE(MEMNAME)=“&TABLE”
AND
UPCASE(MEMTYPE)=“DATA”
;
QUIT;
%MEND NOBS;
%NOBS(WORK
,PRODUCTS);

/* 4.4.2.4 Code Example: Identifying Duplicate Rows in a Table */
%MACRO DUPS(LIB, TABLE
, GROUPBY);
PROC
SQL;
SELECT &GROUPBY, COUNT(*) AS
Duplicate_Rows
FROM &LIB..&
TABLE
GROUP
BY
&GROUPBY
HAVING COUNT
(*) > 1;
QUIT;
%MEND DUPS;
%DUPS(WORK
,PRODUCTS,PRODTYPE);

/**********************************************************/
/*
Chapter 5: Creating, Populating, and Deleting Tables
*/
/**********************************************************/
/*
5.2.1 Code Example: Creating a Table Using Column-Definition Lists #1
*/
PROC
SQL;
CREATE TABLE
CUSTOMERS
(CUSTNUM NUM LABEL=‘Customer Number’
,
CUSTNAME CHAR(25) LABEL=‘Customer Name’
,
CUSTCITY CHAR(20) LABEL=‘Customer”s Home City’
);
QUIT;
/* 5.2.1 Code Example: Creating a Table Using Column-Definition Lists #2
*/
DATA CUSTOMERS;
LENGTH CUSTNUM 3.;
SET CUSTOMERS(DROP
=CUSTNUM);
LABEL CUSTNUM = ‘Customer Number’
;
RUN;
/* 5.2.1 Code Example: Creating a Table Using Column-Definition Lists #3
*/
PROC
SQL;
CREATE TABLE
PRODUCTS
(PRODNUM NUM(3) LABEL=‘Product Number’
,
PRODNAME CHAR(25) LABEL=‘Product Name’
,
MANUNUM NUM(3) LABEL=‘Manufacturer Number’
,
PRODTYPE CHAR(15) LABEL=‘Product Type’
,
PRODCOST NUM(5,2) FORMAT=DOLLAR9.2 LABEL=‘Product Cost’
);
QUIT;

/* 5.2.1 Code Example: Creating a Table Using Column-Definition Lists #4 */
DATA PRODUCTS;
LENGTH PRODNUM MANUNUM 3.
PRODCOST 5.;
SET PRODUCTS(DROP
=PRODNUM MANUNUM PRODCOST);
LABEL PRODNUM = ‘Product
Number’
MANUNUM = ‘Manufacturer
Number’
PRODCOST = ‘Product Cost’
;
FORMAT PRODCOST DOLLAR9.2;
RUN;

/* 5.2.2 Code Example: Creating a Table Using the LIKE Clause #1 */
PROC
SQL;
CREATE TABLE
HOT_PRODUCTS
LIKE
PRODUCTS;
QUIT;

/* 5.2.2 Code Example: Creating a Table Using the LIKE Clause #2 */
PROC
SQL;
CREATE TABLE
HOT_PRODUCTS(KEEP=PRODNAME PRODTYPE PRODCOST)
LIKE
PRODUCTS;
QUIT;

/* 5.2.3 Code Example: Deriving a Table and Data from an Existing Table #1 */
PROC
SQL;
CREATE TABLE HOT_PRODUCTS
AS
SELECT
*
FROM
PRODUCTS;
QUIT;

/* 5.2.3 Code Example: Deriving a Table and Data from an Existing Table #2 */
PROC
SQL;
CREATE TABLE HOT_PRODUCTS
AS
SELECT
*
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) IN (“SOFTWARE”, “PHONE”
);
QUIT;

/* 5.2.3 Code Example: Deriving a Table and Data from an Existing Table #3 */
PROC
SQL;
CREATE TABLE NOT_SO_HOT_PRODUCTS
AS
SELECT
*
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) NOT IN (“SOFTWARE”, “PHONE”
);
QUIT;

/* 5.3.1 Code Example: Adding Data to All the Columns in a Row #1 */
PROC
SQL;
INSERT INTO
CUSTOMERS (CUSTNUM, CUSTNAME, CUSTCITY)
VALUES (702, ‘Mission Valley Computing’, ‘San Diego’
);
QUIT;

/* 5.3.1 Code Example: Adding Data to All the Columns in a Row #2 */
PROC
SQL;
INSERT INTO
CUSTOMERS
(CUSTNUM, CUSTNAME, CUSTCITY)
VALUES (402, ‘La Jolla Tech Center’, ‘La Jolla’
)
VALUES (502, ‘Alpine Byte Center’, ‘Alpine’
)
VALUES (1702,‘Rancho San Diego Tech’,‘Rancho San Diego’
);
SELECT
*
FROM
CUSTOMERS
ORDER BY
CUSTNUM;
QUIT;

/* 5.3.2 Code Example: Adding Data to Some of the Columns in a Row #1 */
PROC
SQL;
INSERT INTO
CUSTOMERS
(CUSTNUM, CUSTNAME)
VALUES (102, ‘La Mesa Byte & Floppy’
)
VALUES (902, ‘Del Mar Technology Center’
);
SELECT
*
FROM
CUSTOMERS
ORDER BY
CUSTNUM;
QUIT;

/* 5.3.2 Code Example: Adding Data to Some of the Columns in a Row #2 */
PROC
SQL;
INSERT INTO
PRODUCTS
(PRODNUM, PRODNAME, PRODTYPE, PRODCOST)
VALUES(6002,‘Security Software’,‘Software’
,375.00)
VALUES(1701,‘Travel Laptop SE’, ‘Laptop’
, 4200.00);
SELECT
*
FROM
PRODUCTS
ORDER BY
PRODNUM;
QUIT;

/* 5.3.3 Code Example: Adding Data with a SELECT Query */
PROC
SQL;
INSERT INTO
PRODUCTS
(PRODNUM, PRODNAME, PRODTYPE, PRODCOST)
SELECT
PRODNUM, PRODNAME, PRODTYPE, PRODCOST
FROM
SOFTWARE_PRODUCTS
WHERE PRODTYPE IN (‘Software’
);
QUIT;

/* 5.4.3 Code Example: Preventing Null Values with a NOT NULL Constraint #1 */
PROC
SQL;
CREATE TABLE
CUSTOMER_CITY
(CUSTNUM NUM,
CUSTCITY CHAR(20) NOT NULL
);
QUIT;

/* 5.4.3 Code Example: Preventing Null Values with a NOT NULL Constraint #2 */
PROC
SQL;
INSERT INTO
CUSTOMER_CITY
VALUES(101,‘La Mesa Computer Land’
)
VALUES(1301,‘Spring Valley Byte Center’
);
QUIT;

/* 5.4.3 Code Example: Preventing Null Values with a NOT NULL Constraint #3 */
PROC
SQL;
INSERT INTO
CUSTOMER_CITY
VALUES(101,‘La Mesa Computer Land’
)
VALUES(1301,‘Spring Valley Byte Center’
)
VALUES(1801,
);
QUIT;

/* 5.4.3 Code Example: Preventing Null Values with a NOT NULL Constraint #4 */
PROC
SQL;
ALTER TABLE
CUSTOMERS
ADD CONSTRAINT NOT_NULL_CUSTCITY NOT NULL
(CUSTCITY);
QUIT;

/* 5.4.4 Code Example: Enforcing Unique Values with a UNIQUE Constraint #1 */
PROC
SQL;
CREATE TABLE
CUSTOMER_CITY
(CUSTNUM NUM UNIQUE
,
CUSTCITY CHAR
(20));
QUIT;

/* 5.4.4 Code Example: Enforcing Unique Values with a UNIQUE Constraint #2 */
PROC
SQL;
INSERT INTO
CUSTOMER_CITY
VALUES(101,‘La Mesa Computer Land’
)
VALUES(1301,‘Spring Valley Byte Center’
)
VALUES(1301,‘Chula Vista Networks’
);
QUIT;

/* 5.4.5 Code Example: Validating Column Values with a CHECK Constraint #1 */
PROC
SQL;
ALTER TABLE
PRODUCTS
ADD CONSTRAINT
CHECK_PRODUCT_TYPE
CHECK (PRODTYPE IN (‘Laptop’
,
‘Phone’
,
‘Software’
,
‘Workstation’
)
);
QUIT;

/* 5.4.5 Code Example: Validating Column Values with a CHECK Constraint #2 */
PROC
SQL;
INSERT INTO
PRODUCTS
VALUES(5005,‘Internet Software’,500,‘Software’
,99.)
VALUES(1701,‘Elite Laptop’,170,‘Laptop’
,3900.)
VALUES(2103,‘Digital Cell Phone’,210,‘Fone’
,199.);
QUIT;

/* 5.4.7 Code Example: Establishing a Primary Key #1 */
PROC
SQL;
ALTER TABLE
MANUFACTURERS
ADD CONSTRAINT PRIM_KEY PRIMARY KEY
(MANUNUM);
QUIT;

/* 5.4.7 Code Example: Establishing a Primary Key #2 */
PROC
SQL;
ALTER TABLE
PRODUCTS
ADD CONSTRAINT PRIM_PRODUCT_KEY PRIMARY KEY
(PRODNUM);
QUIT;

/* 5.4.8 Code Example: Establishing a Foreign Key #1 */
PROC
SQL;
ALTER TABLE
INVENTORY
ADD CONSTRAINT FOREIGN_PRODUCT_KEY FOREIGN KEY
(PRODNUM)
REFERENCES
PRODUCTS
ON DELETE
RESTRICT
ON
UPDATE RESTRICT
;
QUIT;

/* 5.4.8 Code Example: Establishing a Foreign Key #2 */
PROC
SQL;
ALTER TABLE
INVENTORY
ADD CONSTRAINT FOREIGN_MISSING_PRODUCT_KEY FOREIGN KEY
(PRODNUM)
REFERENCES
PRODUCTS
ON DELETE SET NULL
;
QUIT;

/* 5.4.8 Code Example: Establishing a Foreign Key #3 */
PROC
SQL;
ALTER TABLE
INVENTORY
ADD CONSTRAINT FOREIGN_PRODUCT_KEY FOREIGN KEY
(PRODNUM)
REFERENCES
PRODUCTS
ON UPDATE
CASCADE
ON
DELETE RESTRICT /* DEFAULT VALUE */
;
QUIT;

/* 5.4.9 Code Example: Displaying Integrity Constraints */
PROC
SQL;
DESCRIBE TABLE
MANUFACTURERS;
QUIT;

/* 5.5.1 Code Example: Deleting a Single Row in a Table */
PROC
SQL;
DELETE FROM
CUSTOMERS
WHERE UPCASE(CUSTNAME) = “LAUGHLER”
;
QUIT;

/* 5.5.2 Code Example: Deleting More Than One Row in a Table */
PROC
SQL;
DELETE FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) = ‘PHONE’
;
QUIT;

/* 5.5.3 Code Example: Deleting All Rows in a Table */
PROC
SQL;
DELETE FROM
CUSTOMERS;
QUIT;

/* 5.6.1 Code Example: Deleting a Single Table */
PROC
SQL;
DROP TABLE
HOT_PRODUCTS;
QUIT;

/* 5.6.2 Code Example: Deleting Multiple Tables */
PROC
SQL;
DROP TABLE
HOT_PRODUCTS, NOT_SO_HOT_PRODUCTS;
QUIT;

/* 5.6.3 Code Example: Deleting Tables Containing Integrity Constraints #1 */
/*
NOTE: This Code Intentionally Produces an ERROR!
*/
PROC
SQL;
DROP TABLE
INVENTORY;
QUIT;

/* 5.6.3 Code Example: Deleting Tables Containing Integrity Constraints #2 */
PROC
SQL;
ALTER TABLE
INVENTORY
DROP CONSTRAINT
FOREIGN_PRODUCT_KEY;
QUIT;

PROC SQL;
DROP TABLE
INVENTORY;
QUIT;

/**********************************************************/
/*
Chapter 6: Modifying and Updating Tables and Indexes
*/
/**********************************************************/
/*
6.2.1 Code Example: Adding New Columns
*/
PROC
SQL;
ALTER TABLE
INVENTORY
ADD inventory_status char
(12);
QUIT;

/* 6.2.2 Code Example: Controlling the Position of Columns in a Table #1 */
PROC
SQL;
ALTER TABLE
INVENTORY
ADD INVENTORY_STATUS CHAR
(12);
CREATE TABLE INVENTORY_COPY
AS
SELECT
PRODNUM, INVENQTY, ORDDATE, INVENTORY_STATUS,
INVENCST, MANUNUM
FROM
INVENTORY;
QUIT;
PROC
CONTENTS DATA=INVENTORY_COPY;
RUN;

/* 6.2.2 Code Example: Controlling the Position of Columns in a Table #2 */
PROC
SQL;
CREATE VIEW INVENTORY_VIEW
AS
SELECT
PRODNUM, INVENQTY, INVENTORY_STATUS
FROM
INVENTORY;
QUIT;

/* 6.2.3 Code Example: Changing a Column’s Length #1 */
PROC
SQL;
ALTER TABLE
MANUFACTURERS
MODIFY MANUCITY CHAR
(15);
QUIT;

/* 6.2.3 Code Example: Changing a Column’s Length #2 */
PROC
SQL;
CREATE TABLE
MANUFACTURERS_MODIFIED
SELECT
MANUNUM, MANUNAME, MANUCITY LENGTH=15, MANUSTAT
FROM
MANUFACTURERS;
QUIT;

/* 6.2.3 Code Example: Changing a Column’s Length #3 */
PROC
SQL;
CREATE TABLE MANUFACTURERS_MODIFIED
AS
SELECT
MANUNUM LENGTH=4, MANUNAME, MANUCITY, MANUSTAT
FROM
MANUFACTURERS;
QUIT;

/* 6.2.3 Code Example: Changing a Column’s Length #4 */
DATA MANUFACTURERS;
LENGTH MANUNUM 4.;
SET
MANUFACTURERS
RUN;

/* 6.2.4 Code Example: Changing a Column’s Format */
PROC
SQL;
ALTER TABLE
PRODUCTS
MODIFY PRODCOST FORMAT=DOLLAR12.2;
QUIT;

/* 6.2.5 Code Example: Changing a Column’s Label */
PROC
SQL;
ALTER TABLE
PRODUCTS
MODIFY PRODCOST LABEL=“Retail Product Cost”
;
QUIT;

/* 6.2.6 Code Example: Renaming a Column #1 */
PROC
SQL;
CREATE TABLE PURCHASES
AS
SELECT
CUSTNUM, ITEM AS
ITEM_PURCHASED, UNITS, UNITCOST
FROM
PURCHASES;
QUIT;

/* 6.2.6 Code Example: Renaming a Column #2 */
PROC
SQL;
SELECT
*
FROM
PURCHASES (RENAME=ITEM=ITEM_PURCHASED);
QUIT;

/* < or > */
PROC
SQL;
SELECT
*
FROM
PURCHASES (RENAME=(ITEM=ITEM_PURCHASED));
QUIT;

/* 6.2.7 Code Example: Renaming a Table #1 */
PROC
DATASETS LIBRARY=SQL;
CHANGE PRODUCTS = MANUFACTURED_PRODUCTS;
RUN;

/* 6.2.7 Code Example: Renaming a Table #2 */
PROC
SQL;
CREATE TABLE MANUFACTURED_PRODUCTS
AS
SELECT
*
FROM
PRODUCTS;
DROP TABLE
PRODUCTS;
QUIT;

/* 6.3.2 Code Example: Creating a Simple Index */
PROC
SQL;
CREATE INDEX PRODTYPE ON
PRODUCTS(PRODTYPE);
QUIT;

/* 6.3.3 Code Example: Creating a Composite Index */
PROC
SQL;
CREATE
INDEX
MANUNUM_PRODTYPE ON
PRODUCTS(MANUNUM,PRODTYPE);
QUIT;

/* 6.3.6 Code Example: Deleting (Dropping) Indexes #1 */
PROC
SQL;
DROP INDEX
MANUNUM_PRODTYPE
FROM
PRODUCTS;
QUIT;

/* 6.3.6 Code Example: Deleting (Dropping) Indexes #2 */
PROC
SQL;
DROP INDEX
MANUNUM, PRODTYPE
FROM
PRODUCTS;
QUIT;

/* 6.4 Code Example: Updating Data in a Table */
PROC
SQL;
UPDATE
PRODUCTS
SET
PRODCOST = PRODCOST – (PRODCOST * 0.2)
WHERE UPCASE(PRODTYPE) = ‘LAPTOP’
;
SELECT
*
FROM
PRODUCTS;
QUIT;

/**********************************************************/
/*
Chapter 7: Coding Complex Queries
*/
/**********************************************************/
/*
7.4 Code Example: Cartesian Product Joins
*/
PROC
SQL;
SELECT
prodname, prodcost,
manufacturers.manunum, manuname
FROM
PRODUCTS, MANUFACTURERS;
QUIT;

/* 7.5.1 Code Example: Equijoins #1 */
PROC
SQL;
SELECT
prodname, prodcost,
manufacturers.manunum, manuname
FROM
PRODUCTS, MANUFACTURERS
WHERE
products.manunum =
manufacturers.manunum;
QUIT;

/* 7.5.1 Code Example: Equijoins #2 */
PROC
SQL;
SELECT
prodname, prodcost,
manufacturers.manunum, manuname
FROM
PRODUCTS, MANUFACTURERS
WHERE
products.manunum =
manufacturers.manunum
AND
products.manunum = 500;
QUIT;

/* 7.5.1 Code Example: Equijoins #3 */
PROC
SQL;
SELECT DISTINCT SUM(prodcost) AS
Total_Cost
FORMAT=DOLLAR10.2,
manufacturers.manunum
FROM
PRODUCTS, MANUFACTURERS
WHERE
products.manunum = manufacturers.manunum
AND manufacturers.manuname = ‘KPL Enterprises’
;
QUIT;

/* 7.5.2 Code Example: Non-Equijoins */
PROC
SQL;
SELECT
prodname, prodtype, prodcost,
manufacturers.manunum, manufacturers.manuname
FROM
PRODUCTS, MANUFACTURERS
WHERE
manufacturers.manunum = 500
AND prodtype =
‘Software’
AND
prodcost > 299.00;
QUIT;

/* 7.5.3 Code Example: Reflexive or Self Joins #1 */
PROC
SQL;
SELECT
products.prodname, products.prodtype,
products.prodcost,
products_copy.prodnum, products_copy.prodtype,
products_copy.prodcost
FROM
PRODUCTS, PRODUCTS PRODUCTS_COPY
WHERE
products.prodtype = products_copy.prodtype
AND
products.prodcost < products_copy.prodcost;
QUIT;

/* 7.5.3 Code Example: Reflexive or Self Joins #2 */
PROC
SQL;
SELECT
invoice.custnum, invoice.invprice,
invoice_copy.custnum, invoice_copy.invprice
FROM
INVOICE, INVOICE INVOICE_COPY
WHERE
invoice.invprice < invoice_copy.invprice;
QUIT;

/* 7.5.4 Code Example: Using Table Aliases in Joins */
PROC
SQL;
SELECT
prodnum, prodname, prodtype, M.manunum
FROM
PRODUCTS P, MANUFACTURERS M
WHERE
P.manunum = M.manunum
AND M.manuname = ‘Global Software’
;
QUIT;

/* 7.5.5 Code Example: Performing Computations in Joins */
PROC
SQL;
SELECT
prodname, prodtype, prodcost,
prodcost * .0775 AS
SalesTax
FORMAT=dollar10.2 LABEL=‘California Sales
Tax’
FROM
PRODUCTS P, MANUFACTURERS M
WHERE
P.manunum = M.manunum
AND M.manustat = ‘CA’
;
QUIT;

/* 7.5.6 Code Example: Joins with Three Tables #1 */
PROC
SQL;
SELECT
P.prodname,
P.prodcost,
M.manuname,
I.invqty
FROM
PRODUCTS P,
MANUFACTURERS M,
INVOICE I
WHERE
P.manunum = M.manunum
AND
P.prodnum = I.prodnum
AND
M.manunum = 500;
QUIT;

/* 7.5.6 Code Example: Joins with Three Tables #2 */
PROC
SQL;
SELECT
P.prodname,
P.prodcost,
C.custname,
I.invprice
FROM
PRODUCTS P,
INVOICE I,
CUSTOMERS C
WHERE
P.prodnum = I.prodnum
AND
I.custnum = C.custnum;
QUIT;

/* 7.5.7 Code Example: Joins with More Than Three Tables #1 */
PROC
SQL;
SELECT sum
(inventory.invenqty)
AS
Products_Ordered_Before_09012000
FROM
PRODUCTS,
INVOICE,
CUSTOMERS,
INVENTORY
WHERE
inventory.orddate < mdy(09,01,00)
AND
products.prodnum = invoice.prodnum
AND
invoice.custnum = customers.custnum
AND
invoice.prodnum = inventory.prodnum;
QUIT;

/* 7.5.7 Code Example: Joins with More Than Three Tables #2 */
PROC
SQL;
SELECT sum
(inventory.invenqty)
AS
Products_Ordered_Before_09012000
FROM
INVOICE I,
INVENTORY I2
WHERE
inventory.orddate < mdy(09,01,00)
AND
invoice.prodnum = inventory.prodnum;
QUIT;

/* 7.5.7 Code Example: Joins with More Than Three Tables #3 */
PROC
SQL;
SELECT
products.prodname,
products.prodtype,
customers.custname,
manufacturers.manuname
FROM
MANUFACTURERS,
PRODUCTS,
INVOICE,
CUSTOMERS
WHERE
manufacturers.manunum = products.manunum
AND
manufacturers.manunum = invoice.manunum
AND
products.prodnum = invoice.prodnum
AND
invoice.custnum = customers.custnum;
QUIT;

/* 7.6.1 Code Example: Left Outer Joins */
PROC
SQL;
SELECT
manuname, manucity, manufacturers.manunum,
products.prodtype, products.prodcost
FROM
MANUFACTURERS
LEFT JOIN
PRODUCTS
ON
manufacturers.manunum = products.manunum;
QUIT;

/* 7.6.1.1 Code Example: Specifying a WHERE Clause */
PROC
SQL;
SELECT
manuname, manucity, manufacturers.manunum,
products.prodtype, products.prodcost
FROM
MANUFACTURERS
LEFT JOIN PRODUCTS ON
manufacturers.manunum = products.manunum
WHERE
prodcost < 300
AND
prodcost NE .;
QUIT;

/* 7.6.1.2 Code Example: Specifying Aggregate Functions */
PROC
SQL;
SELECT
manuname,
SUM(invoice.invprice) AS
Total_Invoice_Amt
FORMAT=DOLLAR10.2
FROM MANUFACTURERS LEFT JOIN
INVOICE
ON
manufacturers.manunum =
invoice.manunum
GROUP BY
MANUNAME;
QUIT;
/* 7.6.2 Code Example: Right Outer Joins
*/
PROC
SQL;
SELECT
prodname, prodtype,
products.manunum, manuname
FROM
PRODUCTS
RIGHT JOIN MANUFACTURERS ON
products.manunum = manufacturers.manunum;
QUIT;

/* 7.6.3 Code Example: Full Outer Joins */
PROC
SQL;
SELECT
prodname, prodtype,
products.manunum, manuname
FROM
PRODUCTS
FULL JOIN MANUFACTURERS ON
products.manunum = manufacturers.manunum;
QUIT;

/* 7.7.1 Code Example: Alternate Approaches to Subqueries #1 */
PROC
SQL;
SELECT
*
FROM
INVOICE
WHERE
manunum = 210;
QUIT;

/* 7.7.1 Code Example: Alternate Approaches to Subqueries #2 */
PROC
SQL;
SELECT
M.manunum, I.manuname, I.invnum,
I.invqty, I.invprice
FROM
MANUFACTURERS M, INVOICE I
WHERE
M.manunum = I.manunum
AND M.manuname = ‘Global Comm Corp’
;
QUIT;

/* 7.7.2 Code Example: Passing a Single Value with a Subquery #1 */
PROC
SQL;
SELECT
*
FROM
INVOICE
WHERE manunum = (SELECT
manunum
FROM
MANUFACTURERS
WHERE manuname = ‘Global Comm Corp’
);
QUIT;

/* 7.7.2 Code Example: Passing a Single Value with a Subquery #2 */
PROC
SQL;
SELECT
*
FROM
INVOICE
WHERE prodnum = (SELECT
prodnum
FROM
PRODUCTS
WHERE prodname LIKE ‘Dream%’
);
QUIT;

/* 7.7.2 Code Example: Passing a Single Value with a Subquery #3 */
PROC
SQL;
SELECT
*
FROM
INVOICE
WHERE manunum = (SELECT
manunum
FROM
MANUFACTURERS
WHERE UPCASE(manucity) LIKE ‘SAN DIEGO%’
);
QUIT;

/* 7.7.2 Code Example: Passing a Single Value with a Subquery #4 */
PROC
SQL;
SELECT
prodnum, invnum, invqty, invprice
FROM
INVOICE
WHERE invqty < (SELECT AVG
(invqty)
FROM
INVOICE);
QUIT;

/* 7.7.3 Code Example: Passing More Than One Row with a Subquery */
PROC
SQL;
SELECT
*
FROM
INVOICE
WHERE manunum IN (SELECT
manunum
FROM
MANUFACTURERS
WHERE UPCASE(manucity) LIKE ‘SAN DIEGO%’
);
QUIT;

/* 7.7.4 Code Example: Comparing a Set of Values #1 */
PROC
SQL;
SELECT
manunum, prodnum, invqty, invprice
FROM
INVOICE
WHERE invprice GE ANY (SELECT
invprice
FROM
INVOICE
WHERE prodnum IN
(5001,5002));
QUIT;

/* 7.7.4 Code Example: Comparing a Set of Values #2 */
PROC
SQL;
SELECT
manunum, prodnum, invqty, invprice
FROM
INVOICE
WHERE invprice <
ALL
(SELECT
invprice
FROM
INVOICE
WHERE prodnum IN
(5001,5002));
QUIT;

/* 7.7.5 Code Example: Correlated Subqueries #1 */
PROC
SQL;
SELECT
prodnum, prodname, prodtype
FROM
PRODUCTS
WHERE NOT EXISTS (SELECT
*
FROM
INVOICE
WHERE
PRODUCTS.prodnum = INVOICE.prodnum);
QUIT;

/* 7.7.5 Code Example: Correlated Subqueries #2 */
PROC
SQL;
SELECT
prodnum, prodname, prodtype
FROM
PRODUCTS
WHERE EXISTS (SELECT
*
FROM
INVOICE
WHERE
PRODUCTS.manunum = INVOICE.manunum
HAVING COUNT
(*) > 1);
QUIT;

/* 7.8.1 Code Example: Accessing Rows from the Intersection of Two Queries #1 */
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE prodcost < 300.00
AND
prodtype = ‘Phone’
;
QUIT;

/* 7.8.1 Code Example: Accessing Rows from the Intersection of Two Queries #2 */
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE
prodcost < 300.00
INTERSECT
SELECT
*
FROM
PRODUCTS
WHERE prodtype = “Phone”
;
QUIT;

/* 7.8.2 Code Example: Accessing Rows from the Combination of Two Queries #1 */
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE prodcost < 300.00
OR
prodtype = “Workstation”
;
QUIT;

/* 7.8.2 Code Example: Accessing Rows from the Combination of Two Queries #2 */
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE
prodcost < 300.00
UNION
SELECT
*
FROM
PRODUCTS
WHERE prodtype = ‘Workstation’
;
QUIT;

/* 7.8.3 Code Example: Concatenating Rows from Two Queries #1 */
PROC
SQL;
SELECT
prodnum, prodname, prodtype, prodcost
FROM
PRODUCTS
OUTER
UNION
SELECT
prodnum, prodname, prodtype, prodcost
FROM
PRODUCTS;
QUIT;

/* 7.8.3 Code Example: Concatenating Rows from Two Queries #2 */
PROC
SQL;
SELECT
prodnum, prodname, prodtype, prodcost
FROM
PRODUCTS
OUTER UNION
CORR
SELECT
prodnum, prodname, prodtype, prodcost
FROM
PRODUCTS;
QUIT;

/* 7.8.4 Code Example: Comparing Rows from Two Queries */
PROC
SQL;
SELECT
*
FROM
CUSTOMERS
EXCEPT
SELECT
*
FROM
CUSTOMERS_BACKUP;
QUIT;

/**********************************************************/
/*
Chapter 8: Working with Views
*/
/**********************************************************/
/*
8.2.3 Code Example: Creating Views #1
*/
PROC
SQL;
CREATE VIEW MANUFACTURERS_VIEW
AS
SELECT
manuname, manunum, manucity, manustat
FROM
MANUFACTURERS;
QUIT;

/* 8.2.3 Code Example: Creating Views #2 */
PROC
SQL;
CREATE VIEW INVENTORY_VIEW
AS
SELECT
prodnum, invenqty, invencst,
invencst/invenqty AS
AverageAmount
FROM
INVENTORY;
QUIT;

/* 8.2.4 Code Example: Displaying a View’s Contents */
PROC
CONTENTS DATA=INVENTORY_VIEW;
RUN;

/* 8.2.5 Code Example: Describing View Definitions */
PROC
SQL;
DESCRIBE VIEW
INVENTORY_VIEW;
QUIT;

/* 8.2.6 Code Example: Creating and Using Views in the SAS System */
/*
NOTE: This code Intentionally Produces an Error!
*/
PROC
SQL;
CREATE VIEW NO_CAN_DO_VIEW
AS
SELECT
*
FROM
NO_CAN_DO_VIEW;
SELECT
*
FROM
NO_CAN_DO_VIEW;
QUIT;

/* 8.2.7 Code Example: Views and SAS Procedures #1 */
PROC
MEANS DATA=INVENTORY_VIEW;
TITLE1 ‘Inventory Statistical Report’
;
TITLE2 ‘Demonstration of a View used in PROC MEANS’
;
RUN;

/* 8.2.7 Code Example: Views and SAS Procedures #2 */
PROC PRINT
DATA=INVENTORY_VIEW N NOOBS UNIFORM;
TITLE1 ‘Inventory Detail Listing’
;
TITLE2 ‘Demonstration of a View used in PROC PRINT’
;
FORMAT AverageAmount dollar10.2;
RUN;

/* 8.2.8 Code Example: Views and DATA Steps */
DATA _NULL_;
SET
INVENTORY_VIEW (KEEP=PRODNUM AVERAGEAMOUNT);
FILE PRINT
HEADER=H1;
PUT @10 PRODNUM
@30 AVERAGEAMOUNT DOLLAR10.2;
RETURN
;
H1: PUT @9 ‘Using a View in a DATA
Step’
/// @5 ‘Product
Number’
@26 ‘Average Amount’
;
RETURN
;
RUN;

/* 8.4 Code Example: Restricting Data Access Security #1 */
PROC
SQL;
CREATE VIEW SOFTWARE_PRODUCTS_VIEW
AS
SELECT
prodnum, prodname, manunum, prodtype
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) IN (‘SOFTWARE’
);
QUIT;

/* 8.4 Code Example: Restricting Data Access Security #2 */
PROC
SQL;
SELECT
*
FROM
SOFTWARE_PRODUCTS_VIEW
ORDER BY
prodname;
QUIT;

/* 8.5 Code Example: Hiding Logic Complexities #1 */
PROC
SQL;
CREATE VIEW PROD_MANF_VIEW
AS
SELECT
DISTINCT SUM
(prodcost) FORMAT=DOLLAR10.2,
M.manunum,
M.manuname
FROM PRODUCTS AS P, MANUFACTURERS AS
M
WHERE P.manunum = M.manunum
AND
M.manuname = ‘KPL Enterprises’
;
QUIT;

/* 8.5 Code Example: Hiding Logic Complexities #2 */
PROC
SQL;
SELECT
*
FROM
PROD_MANF_VIEW;
QUIT;

/* 8.6 Code Example: Nesting Views #1 */
PROC
SQL;
CREATE VIEW WORKSTATION_PRODUCTS_VIEW
AS
SELECT
PRODNUM, PRODNAME, PRODTYPE, PRODCOST
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE)=“WORKSTATION”
;
QUIT;

/* 8.6 Code Example: Nesting Views #2 */
PROC
SQL;
CREATE VIEW INVOICE_1K_VIEW
AS
SELECT
INVNUM, CUSTNUM, PRODNUM, INVQTY, INVPRICE
FROM
INVOICE
WHERE
INVPRICE >= 1000.00;
QUIT;

/* 8.6 Code Example: Nesting Views #3 */
PROC
SQL;
CREATE VIEW JOINED_VIEW
AS
SELECT
V1.PRODNUM, V1.PRODNAME,
V2.CUSTNUM, V2.INVQTY, V2.INVPRICE
FROM
WORKSTATION_PRODUCTS_VIEW V1,
INVOICE_1K_VIEW V2
WHERE
V1.PRODNUM = V2.PRODNUM;
QUIT;

/* 8.6 Code Example: Nesting Views #4 */
PROC
SQL;
CREATE VIEW LARGEST_AMOUNT_VIEW
AS
SELECT
MAX(INVPRICE*INVQTY) AS
Maximum_Price
FORMAT=DOLLAR12.2
LABEL=“Largest Invoice
Amount”
FROM
JOINED_VIEW;
QUIT;

/* 8.7.1 Code Example: Inserting New Rows of Data #1 */
PROC
SQL;
CREATE VIEW SOFTWARE_PRODUCTS_VIEW
AS
SELECT
prodnum, prodname, prodtype, prodcost
FORMAT=DOLLAR8.2
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) IN (‘SOFTWARE’
);
QUIT;

/* 8.7.1 Code Example: Inserting New Rows of Data #2 */
PROC
SQL;
INSERT INTO
SOFTWARE_PRODUCTS_VIEW
VALUES(6002,‘Security Software’,‘Software’
,375.00);
QUIT;

/* 8.7.1 Code Example: Inserting New Rows of Data #3 */
/*
NOTE: This Code Intentionally Produces an Error
*/
PROC
SQL;
INSERT INTO
SOFTWARE_PRODUCTS_VIEW
VALUES(1701,‘Travel Laptop SE’,‘Laptop’
,4200.00);
QUIT;

/* 8.7.1 Code Example: Inserting New Rows of Data #4 */
/*
NOTE: This Code Intentionally Produces an Error
*/
PROC
SQL;
INSERT INTO
SOFTWARE_PRODUCTS_VIEW
VALUES(6003,‘Cleanup Software’,‘Software’
);
QUIT;

/* 8.7.1 Code Example: Inserting New Rows of Data #5 */
PROC
SQL;
CREATE VIEW SOFTWARE_PRODUCTS_TAX_VIEW
AS
SELECT
prodnum, prodname, prodtype, prodcost,
prodcost * .07 AS
Tax
FORMAT=DOLLAR8.2 LABEL=‘Sales
Tax’
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) IN (‘SOFTWARE’
);
QUIT;

/* 8.7.1 Code Example: Inserting New Rows of Data #6 */
/*
NOTE: This Code Intentionally Produces a Warning!
*/
PROC
SQL;
INSERT INTO
SOFTWARE_PRODUCTS_TAX_VIEW
VALUES(6003,‘Cleanup Software’,‘Software’
,375.00,26.25);
QUIT;

/* 8.7.2 Code Example: Updating Existing Rows of Data #1 */
PROC
SQL;
CREATE VIEW LAPTOP_PRODUCTS_VIEW
AS
SELECT
PRODNUM, PRODNAME, PRODTYPE, PRODCOST
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) = ‘LAPTOP’
;
QUIT;

/* 8.7.2 Code Example: Updating Existing Rows of Data #2 */
PROC
SQL;
UPDATE
LAPTOP_PRODUCTS_VIEW
SET
PRODCOST = PRODCOST – (PRODCOST * 0.2);
QUIT;

/* 8.7.2 Code Example: Updating Existing Rows of Data #3 */
PROC
SQL;
CREATE VIEW LAPTOP_DISCOUNT_VIEW
AS
SELECT
PRODNUM, PRODNAME, PRODTYPE, PRODCOST
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) = ‘LAPTOP’
AND
PRODCOST > 2800.00;
QUIT;

/* 8.7.2 Code Example: Updating Existing Rows of Data #4 */
PROC
SQL;
UPDATE
LAPTOP_DISCOUNT_VIEW
SET
PRODCOST = PRODCOST – (PRODCOST * 0.2);
QUIT;

/* 8.7.3 Code Example: Deleting Rows of Data */
PROC
SQL;
DELETE FROM
SOFTWARE_PRODUCTS_VIEW
WHERE
MANUNUM=600;
QUIT;

/* 8.8 Code Example: Deleting Views #1 */
PROC
SQL;
DROP VIEW
INVENTORY_VIEW;
QUIT;

/* 8.8 Code Example: Deleting Views #2 */
PROC
SQL;
DROP VIEW
INVENTORY_VIEW, LAPTOP_PRODUCTS_VIEW;
QUIT;

/**********************************************************/
/*
Chapter 9: Troubleshooting and Debugging Techniques
*/
/**********************************************************/
/*
9.5.1 Code Example: Validating Queries with the VALIDATE Statement
*/
PROC
SQL;
VALIDATE
SELECT
*
FROM
PRODUCTS
WHERE PRODTYPE = ‘Software’
;
QUIT;

/* 9.5.2.1 Code Example: FEEDBACK Option #1 */
PROC
SQL FEEDBACK;
SELECT
*
FROM
PRODUCTS;
QUIT;

/* 9.5.2.1 Code Example: FEEDBACK Option #2 */
PROC
SQL FEEDBACK;
SELECT
*
FROM
PRODUCTS, MANUFACTURERS
WHERE
PRODUCTS.MANUNUM = MANUFACTURERS.MANUNUM
AND MANUFACTURERS.MANUNAME = ‘KPL Enterprises’
;
QUIT;

/* 9.5.2.1 Code Example: FEEDBACK Option #3 */
%MACRO DUPS(LIB, TABLE
, GROUPBY);
PROC
SQL FEEDBACK;
SELECT &GROUPBY, COUNT(*) AS
Duplicate_Rows
FROM &LIB..&
TABLE
GROUP
BY
&GROUPBY
HAVING COUNT
(*) > 1;
QUIT;
%MEND DUPS;
%DUPS(WORK
,PRODUCTS,PRODTYPE);

/* 9.5.2.1 Code Example: FEEDBACK Option #4 */
%MACRO DUPS(LIB, TABLE
, GROUPBY);
PROC
SQL;
SELECT &GROUPBY, COUNT(*) AS
Duplicate_Rows
FROM &LIB..&
TABLE
GROUP
BY
&GROUPBY
HAVING COUNT
(*) > 1;
QUIT;

%PUT LIB = &LIB TABLE = &TABLE GROUPBY = &GROUPBY;
%MEND DUPS;
%DUPS(WORK
,PRODUCTS,PRODTYPE);
/* 9.5.2.2 Code Example: INOBS= Option #1
*/
PROC
SQL INOBS=10;
SELECT
*
FROM
PRODUCTS;
QUIT;

/* 9.5.2.2 Code Example: INOBS= Option #2 */
PROC
SQL INOBS=5;
SELECT
prodname, prodcost,
manufacturers.manunum, manuname
FROM
PRODUCTS, MANUFACTURERS;
QUIT;

/* 9.5.2.3 Code Example: LOOPS= Option #1 */
PROC
SQL LOOPS=8;
SELECT
*
FROM
PRODUCTS;
QUIT;

/* 9.5.2.3 Code Example: LOOPS= Option #2 */
PROC
SQL LOOPS=50;
SELECT
P.prodname, P.prodcost,M.manuname,I.invqty
FROM
PRODUCTS P,
MANUFACTURERS M,
INVOICE I
WHERE P.manunum = M.manunum

AND P.prodnum = I.prodnum

AND M.manunum = 500;
QUIT;

/* 9.5.2.4 Code Example: NOEXEC Option */
PROC
SQL NOEXEC;
CREATE TABLE
NOEXEC_CHECK
SELECT
*
FROM
PRODUCTS
WHERE PRODTYPE = ‘Software’
;
QUIT;

/* 9.5.2.5 Code Example: OUTOBS= Option */
PROC
SQL OUTOBS=5;
CREATE TABLE PRODUCTS_SAMPLE
AS
SELECT
*
FROM
PRODUCTS;
QUIT;

/* 9.5.2.6 Code Example: PROMPT Option */
PROC
SQL PROMPT INOBS=5;
SELECT
*
FROM
PRODUCTS;
QUIT;

/* 9.5.2.7 Code Example: RESET Statement #1 */
PROC
SQL FEEDBACK;
SELECT
*
FROM
PRODUCTS;
RESET NOFEEDBACK;
SELECT
*
FROM
PRODUCTS
WHERE PRODTYPE=‘Software’
;
QUIT;

/* 9.5.2.7 Code Example: RESET Statement #2 */
PROC SQL DOUBLE
NUMBER OUTOBS=1;
SELECT
*
FROM
PRODUCTS
WHERE PRODTYPE=‘Software’
;
RESET NODOUBLE NONUMBER OUTOBS=MAX
;
SELECT
*
FROM
PRODUCTS
WHERE PRODTYPE=‘Software’
;
QUIT;

/* 9.5.2.7 Code Example: RESET Statement #3 */
/*
NOTE: This Code Intentionally Produces a Warning!
*/
PROC
SQL UNDO_POLICY=REQUIRED;
UPDATE
PRODUCTS
SET
PRODCOST = PRODCOST – (PRODCOST * 0.2)
WHERE UPCASE(PRODTYPE) = ‘LAPTOP’
;
RESET UNDO_POLICY=NONE;
UPDATE
PRODUCTS
SET
PRODCOST = PRODCOST – (PRODCOST * 0.2)
WHERE UPCASE(PRODTYPE) = ‘LAPTOP’
;
QUIT;

/* 9.6 Code Example: Undocumented PROC SQL Options */
PROC
SQL _TREE;
SELECT
*
FROM
PRODUCTS
WHERE PRODTYPE = ‘Software’
;
QUIT;

/* 9.6.1.2 Code Example: SQLOBS Macro Variable #1 */
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE PRODTYPE=‘Software’
;
%PUT SQLOBS = &SQLOBS;
QUIT;

/* 9.6.1.2 Code Example: SQLOBS Macro Variable #2 */
PROC
SQL;
INSERT INTO
PRODUCTS
(PRODNUM, PRODNAME, PRODTYPE, PRODCOST)
VALUES(6002,‘Security Software’,‘Software’
,375.00)
VALUES(1701,‘Travel Laptop SE’, ‘Laptop’
, 4200.00);
%PUT SQLOBS = &SQLOBS;
QUIT;

/* 9.6.1.3 Code Example: SQLOOPS Macro Variable */
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE PRODTYPE=‘Software’
;
%PUT SQLOOPS = &SQLOOPS;
QUIT;

/* 9.6.1.4 Code Example: SQLRC Macro Variable */
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE PRODTYPE=‘Software’
;
%PUT SQLRC = &SQLRC;
QUIT;

/**********************************************************/
/*
Chapter 10: Tuning for Performance and Efficiency
*/
/**********************************************************/
/*
10.4 Code Example: Splitting Tables
*/
PROC
SQL;
CREATE TABLE INVENTORY_CURRENT
AS
SELECT
*
FROM
INVENTORY
WHERE YEAR(ORDDATE) = YEAR
(TODAY());
DELETE FROM
INVENTORY
WHERE YEAR(ORDDATE) = YEAR
(TODAY());
QUIT;

/* 10.6 Code Example: Reviewing CONTENTS Output and System Messages */
PROC
SQL;
SELECT
MEMNAME, NPAGE
FROM
DICTIONARY.TABLES
WHERE LIBNAME=‘WORK’
AND
MEMNAME=‘INVENTORY’
;
SELECT
VARNUM, NAME, TYPE, LENGTH, FORMAT,
INFORMAT, LABEL
FROM
DICTIONARY.COLUMNS
WHERE LIBNAME=‘WORK’
AND
MEMNAME=‘INVENTORY’
;
QUIT;

/* 10.7.1 Code Example: Constructing Efficient Logic Conditions #1 */
/*
NOTE: Less Efficient Code
*/
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) = ‘SOFTWARE’
AND
PRODCOST < 100.00;
QUIT;

/* 10.7.1 Code Example: Constructing Efficient Logic Conditions #2 */
/*
NOTE: More Efficient Code
*/
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE PRODCOST < 100.00
AND
UPCASE(PRODTYPE) = ‘SOFTWARE’
;
QUIT;

/* 10.7.1 Code Example: Constructing Efficient Logic Conditions #3 */
/*
NOTE: Less Efficient Code
*/
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) IN (‘LAPTOP’, ‘SOFTWARE’
);
QUIT;

/* 10.7.1 Code Example: Constructing Efficient Logic Conditions #4 */
/*
NOTE: More Efficient Code
*/
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) IN (‘SOFTWARE’, ‘LAPTOP’
);
QUIT;

/* 10.7.2 Code Example: Avoiding UNIONs #1 */
/*
NOTE: Less Efficient Code
*/
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) =
‘LAPTOP’
  
UNION
SELECT
*
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) = ‘SOFTWARE’
;
QUIT;

/* 10.7.2 Code Example: Avoiding UNIONs #2 */
/*
NOTE: More Efficient Code
*/
PROC
SQL;
SELECT DISTINCT
*
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) = ‘SOFTWARE’
OR
UPCASE(PRODTYPE) = ‘LAPTOP’
;
QUIT;

/* */
PROC
SQL;
SELECT DISTINCT
*
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) IN (‘SOFTWARE’, ‘LAPTOP’
);
QUIT;

/* 10.7.2 Code Example: Avoiding UNIONs #3 */
/*
NOTE: Less Efficient Code
*/
PROC
SQL;
SELECT
*
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) =
‘LAPTOP’
    UNION
ALL
SELECT
*
FROM
PRODUCTS
WHERE UPCASE(PRODTYPE) = ‘SOFTWARE’
;
QUIT;

 

RELATED POSTS

The Complete Guide to SAS Arrays

Are you looking to become a more efficient Data Step programmer? Do you often needRead More

Top 10 Most Powerful Functions for PROC SQL

PROC SQL is not only one of the many SAS procedures and also a distinctiveRead More

  • PROC IMPORT
  • PROC Export
  • Automatically assign libraries at startup
  • SAS Shortcuts