Know How:BusinessIntelligence:SAS:SQL

Hauptseite > Know How > Business Intelligence > SAS > SQL

SAS

Allgemeines Glossar • Dokumentation Themen Sicherheit • Installation • Konfiguration • Administration • Troubleshooting Links ImportExport • Macro Solutions Risk Dimensions • xxx Tipps Metadaten • Makros • Java • NET • JSP • DATA Step • SQL • RegExp • Hash • Formate • Information Maps Tipps:Reporting WebReportStudio • Information Maps ODS Allgemein • PROC TEMPLATE • Links Internationalization Allgemein • Konfiguration

---

Syntax

• http://www.masil.org/sas/sas_sql.html

PROC SQL

Pattern Matching

• Wildcard:

1.  % beliebig viele Zeichen (auch keines)
2. _ genau ein beliebige Zeichen

• Maskieren der Wildcards mit 'escape'

Suchen aller Einträge, die mit AB dann zwei Zeichen und dann dem Zeichen '_' beginnen.

proc sql;
   select *
   from   dataset
   where  col like 'AB__*_' escape '*';
quit;

Basics

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: 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 */

JOINING TABLES: 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 TABLES: Example

Left Join

select     ONE.KEY as KEY_1,
           TWO.KEY as KEY_2,
           ONE.*
from       table1 ONE
left join  table2 TWO
on         (ONE.FIELD1 = TWO.FIELD1)

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

MODIFYING TABLES: 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;

MODIFYING TABLES: 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

  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;