SAS/SQL and Structured Query Language

This document summarizes the usage of SAS/SQL. The Structured Query Language (SQL) is a standardized language that is used to retrieve and manipulate data in relational tables and databases.

SAS/SQL BASICS

Basic Rules

The PROC SQL ends with the "QUIT;" statement.
Individual SQL statements are located between "PROC SQL;" and "QUIT;"
Relational: <, <=, >, >=, = (equal) and <> (not equal)
Logical: AND, OR, NOT

Pattern Matching

var LIKE "b%"; // beginning with b or B
var LIKE "%ent"; // ending with "ent"
var LIKE "%Hun%"; // containing "Hun"
var LIKE "-------"; // containing seven characters
LIKE: "WHERE name LIKE 'P%'"; "WHERE name LIKE '___k'";
IN: "WHERE year (1987, 1991, 1993);
BETWEEN: "WHERE earning BETWEEN 2000 AND 50000";
NULL: IS NULL;' IS NOT NULL;
IS MISSING: "WHERE employer IS MISSING";

CREATE AND DELETE TABLES

CREATE TABLE table ( id char(7), name char(30));
CREATE TABLE table AS SELECT variables FROM table WHERE expression ORDER BY variables DESC;
DROP TABLE table;

SELECT STATEMENT

Select General

SELECT * FROM tables;
SELECT variables FROM tables/views WHERE conditions GROUP BY variables HAVING expression ORDER BY variables;
SELECT * FROM tables INNER JOIN table ON conditions;
SELECT member.id, member.name, feepayment.year, feepayment.month, feepayment.amount FROM sql.member, sql.feepayment WHERE member.id=feepayment.id;
SELECT m.id, m.name, f.year, f.month, f.amount FROM sql.member AS m, sql.feepayment AS f WHERE m.id=f.id; /* Using Aliases */

Selecting by Joining

SELECT [Indiana NPO (Working)].address, followup.address FROM followup INNER JOIN [Indiana NPO (Working)] ON followup.IDS = [Indiana NPO (Working)].IDS WHERE ((([Indiana NPO (Working)].ORGNAME) Is Null));
SELECT [Indiana NPO (Working)].name, followup.name, [Indiana NPO (Working)].address, followup.address FROM [Indiana NPO (Working)] LEFT JOIN followup ON [Indiana NPO (Working)].id=followup.id WHERE ((([Indiana NPO (Working)].address)<>[followup].[address]));
SELECT [Indiana NPO (Working)].name, tracking.name, [Indiana NPO (Working)].address, tracking.address, [Indiana NPO (Working)].city, tracking.city, [Indiana NPO (Working)].phone, tracking.phone INTO member FROM [Indiana NPO (Working)] INNER JOIN tracking ON [Indiana NPO (Working)].id=tracking.id; /* Making a separate table with records that meet the conditions */

JOINING TABLES

Joining General

INNER: Listing only those both sides are equal.
LEFT: Listing all records from the primary side (left hand side) and only those from the right hand side when joined fields are equal
RIGHT: Listing all records from the right hand side and only those from the left hand side when joined fields are equal
... FROM left-hand-side INNER JOIN right-hand-side ON conditions;
... FROM left-hand-side AS lhs INNER JOIN right-hand-side AS rhs ON conditions;

Joining Example

SELECT lhs.name, rhs.name FROM members AS lhs INNER JOIN publish AS rhs ON lhs.id = rhs.id;
SELECT [Indiana NPO (Working)].name, followup.name, [Indiana NPO (Working)].address, followup.address FROM [Indiana NPO (Working)] LEFT JOIN followup ON [Indiana NPO (Working)].id=followup.id WHERE ((([Indiana NPO (Working)].address)<>[followup].[address]));
UPDATE [Indiana NPO (Working)] RIGHT JOIN followup ON [Indiana NPO (Working)].id=followup.id SET [Indiana NPO (Working)].email=followup.email, [Indiana NPO (Working)].webpage=followup.webpage;
INSERT INTO members SELECT FROM [Indiana NPO (Working)] INNER JOIN followup ON [Indiana NPO (Working)].id=followup.id;

MODIFYING TABLES

Insert

INSERT INTO table SET expression WHERE conditions;
INSERT INTO table SET id='8740031', name='JeeShim';
INSERT INTO table VALUES ('9101321', 'kucc625');
INSERT INTO members SELECT FROM [Indiana NPO (Working)] INNER JOIN followup ON [Indiana NPO (Working)].id=followup.id;
INSERT INTO table SELECT FROM lhs INNER JOIN rhs ON lhs.id=rhs.id; /* Appending joined records to the table */

Update

UPDATE table SET expressions WHERE conditions;
UPDATE tracking SET tracking.state="GA", tracking.city="Atanta" WHERE tracking.address IS NOT NULL;
UPDATE [Indiana NPO (Working)] RIGHT JOIN followup ON [Indiana NPO (Working)].id=followup.id SET [Indiana NPO (Working)].email=followup.email, [Indiana NPO (Working)].webpage=followup.webpage;

Delete

DELETE FROM table WHERE expression;
DELETE FROM tracking WHERE (((tracking.ADDRESS1) Is Null));
DELETE tracking.name, tracking.address FROM tracking WHERE (((tracking.state) <>"IN"));

SQL EXAMPLES

Computing Frequencies

PROC SQL;
SELECT name AS Names, Count(Names) AS Frequency
   FROM publish
   GROUP BY Names
   HAVING (((Count(Names))>=1));

Inner Join to get from both Tables

SELECT m.id, m.name, p.journal
FROM members AS m INNER JOIN publish AS p ON m.name = p.name;

Left join

CREATE TABLE left AS SELECT m.id, m.name, i.journal
FROM members AS m LEFT JOIN inner As i ON m.name = i.name;

This step gets the observations appearing in the primary data set and to match observations in secondary data set.

To Get Unique Observation

DATA final;
SET left;
IF journal=MISSING;
RUN;

REFERENCES

SAS Institute Inc. 1989. SAS Guide to the SQL Procedure: Usage and Reference Version 6.