SAS/SQL

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;

Modify

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;