Korea Hangeul Help Sitemap Calc Link KB
SAS/SQL


    Basics | Tables | Selecting | Joining Tables | Modifying Tables | Examples | References

SAS icon SAS/SQL BASICS

MySql SQL PDF icon

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";

SAS icon 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;

SAS icon 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 */

SAS icon 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;

SAS icon 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"));

SAS icon 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;

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