DOCX

SAS Advance Note

By Lee Thomas,2014-04-21 00:31
10 views 0
SAS

    SAS Advance Note

    目录

    Chapter1;Performing Queries Using PROC SQL ........................................................................... 2 Notice .......................................................................................................................................... 2

    PROC SQL basic knowledge ......................................................................................................... 3

    PROC SQL is unique ..................................................................................................................... 3

    Point to Remember ..................................................................................................................... 4

    Chapter2;Performing Advanced Queries Using PROC SQ ............................................................ 4 Notice .......................................................................................................................................... 4

    PROC SQL basic knowledge ......................................................................................................... 5

    Point to Remember ..................................................................................................................... 8

    Chapter3;Combining Tables Horizontally Using PROC SQL .......................................................... 9 Notice .......................................................................................................................................... 9

    Chapter4;Combining Tables Vertically Using PROC SQL ............................................................ 11 Notice ........................................................................................................................................ 11

    Point to Remember ................................................................................................................... 14

    Chapter 5: Creating and Managing Tables Using PROC SQL ......................................................... 14 Notice: ....................................................................................................................................... 14

    Chapter6;Creating and Managing Indexs Using PROC SQL ........................................................ 23 Notice ........................................................................................................................................ 23

    Chapter7;Creating and Managing Views Using PROC SQL ......................................................... 27 Notice ........................................................................................................................................ 27

    Chapter8;Managing Processing Using PROC SQL ....................................................................... 30 Notice ........................................................................................................................................ 30

Chapter1;Performing Queries Using PROC SQL

    Notice

     1.There is the child item in PROC SQL? which is always

    separated bycommas?not just blanks.

     2.What is the difference between the PROC SQL statement, its

    clauses.

     3.The way use as key word when completing the SELECT

    clause below to create a new column.

     4.When using a GROUP BY clause in a PROC SQL step without

    a summary function, the GROUP BY clause is changed to an

    ORDER BY clause.

     5.When you specify a CREATE TABLE statement in your PROC

    SQ step, a new table is created, but no report is displayed.

     6.If two tables that are being joined contain a same-named

    column, then you must specify the table from which you

    want the column to be read.

    Remember that if you join tables that does't contain columns that have matching data values, you can produce a huge amount of output. Be sure to specify a WHERE clause to select only the rows that you want.

     7.When you submit a PROC SQL step without ending it, the

    status line displays the message PROC SQL running. As a

    precaution, SAS Enterprise Guide automatically adds a QUIT

    statement to your code when you submit it to SAS.

    However, you should get in the habit of adding the QUIT

    statement to your code.

     8.The order of Clauses must follows, there is PROC SQL;

    SELECT

    FROM

    WHERE

    GROUP BY

    ORDER BY

     9.Unlike PROC sort procedures, the clause of ORDER BY indicates the sort order is after the column variable, not before the column variable.

    PROC SQL basic knowledge

     1.Retrieve data from and manipulate SAS tables.

     2.Add or modify data values in a table.

     3.Add, modify, or drop columns in a table.

     4.Create tables and views.

     5.Join multiple tables (whether or not they contain columns with the same name).

     6.Generate reports.

    PROC SQL is unique

     1.Unlike other PROC statement, many statement in PROC SQL are composed of clauses.

     2.The PROC SQL step does not require a RUN statement. PROC SQL executes each qurey automatically. If you use a

    RUN statement with a PROC SQL step, SAS ignore the RUN

    statement, executes the statements as usual, and

    gengerates the note shown in the SAS log.

     3.Unlike many other SAS procedures, PROC SQL continues to

    run after you submit a step. To end the procedure, you

    must submit another PROC step, a DATA step, or a QUIT

    statement.

Point to Remember

     1.Do not use a RUN statement with the SQL procedure.

     2.Do not end a clause with a semicolon unless it is the last

    clause in the statement.

     3.When you join multiple table, be sure to specify columns

    that having matching data values in the WHERE clauses in

    order to avoid unwanted combinations.

     4.To end the SQL procedure, you can submit another PROC

    step, a DATA step, or a QUIT statement.

    Chapter2;Performing Advanced Queries Using PROC SQ

    Notice

     7.Ifyou want to remove duplicate values from PROC SQL

    output, you need specify the keyword distinct keyword

    before the column name in the select clauses without parentheses.

     8.To list rows that have no data (that is, missing data), you can use either of these other conditions operators: IS MISSING or IS NULL (IS NOT MISSING, IS NOT NULL). The EXISTS/NOT EXISTS operator is used specifically with a subquery, and resolves to true if the subquery returns no values to the outer query.

     9.When a WHERE clauses references a new column that was defined in the SELECT clause, the WHERE clause must specify the keyword CALCULATED before the column name , without a need of the keyword CALCULATED before the column name in SELECT clauses.

     10.A noncorrelated subquery is a nested query that executes independently of the outer query. The outer query passes no value to the subquery.

    PROC SQL basic knowledge

    1. Using the FEEDBACK Option: The FEEDBACK option is

    debugging tool that lets you see exactly what is being

    submitted to the SQL processor.

    2. Limiting the Number of Rows Displayed: The OUTOBS =

    option restricts the rows that are displayed, but not the

    rows that are read. To restrict the number of rows that

    PROC SQL takes as input from any single source, use the

    INOBS = option.

    3. Using the BETWEEN-AND Operator to Select within a

    Range of Values.

    4. Using the CONTAINS or Question Mark (?) Operator to

    Select a String.

    5. Using the IN operator to Select Values from a List. 6. Using the IS MISSING or IS NULL Operator to Select

    Missing Values.

    7. Using the LIKE Operator to Select a Pattern.(undersocre(_)-

    any single character;

    8. percent sing(%)-any sequence of zero or more character) 9. Using the Sound-Like (=*) Operator to Select a Spelling

    Variation

    10. Subsetting Rows by Using Calculated Values, Using a

    key word CALCULATED.

    11. Enhaceing Query Output: 1. column labels and

    formats; 2. title and footnotes; 3. columns that contain a

    character constant

    12. Summarizing and Grouping Data

    1. Number of Arguments and Summary functon

    Processing: Summary function specify one or more

    arugments in parentheses. The ANSI-stantdard

    summary function, such as AVG and COUNT, can

    only be used with a single argument.

    2. Summary funcitons perform calculations on

    group by of data. When PROC SQL processes a

    summary function, it looks for a GROUP BY clause. 3. A SELECT clause that contains a summary function

    can also list additional columns that are not

    specified in the summary function. The presence

    of theses additional columns in the SELECT clause

    list causes PROC SQL to display the output

    differently.

    4. Using a Summary Functions with a Single

    Argument (Column).

    5. Using a Summary Function with Multiple

    Arguments (Columns).

    6. Using a Summary Function without a GROUP BY

    Clause.

    7. Using a Summary Functions with Columns Outside

    of the Function.

    8. Using a Summary Function with a GROUP BY

    Clause.

    9. Counting Values by Using the COUNT Summary

    Function. {COUNT(*); COUNT(column);

    COUNT(DISTINCT column)}

    10. Counting ALL Non-Missig Values in a Column. 11. Selecting Groups by Using the HAVING Clause.

    13. Using Multiple-value Noncorrelated or Correlated Subqueries.

    (1) The condition operator IN

    (2) A comparison operator that is modified by ANY or

    ALL

    (3) The Conditional operator EXISTS.

    14. Validating Query Syntax.

    (1) the NONEXEC option in the PROC SQL statement. (2) the VALIDATE keyword before a SELECT statement. (3) The main difference between the VALIDATE keyword

    and the NOEXEC option is that the VALIDATE

    keyword only affects the SELECT statement that

    immediately follows it, whereas the NOEXEC option

    applies to all queries in the PROC SQL step.

    Point to Remember

     1.When you use summary functions, look for missing values. If a table contains missing values, your results might not be what you expect. Many summary functions ignore missing values when performing calculations, and PROC SQL treats missing values in a column as a single group.

     2.When you create complex queries, it is helpful to use the NOEXEC option or the VALIDATE statement to validate your query without executing it.

    Chapter3;Combining Tables Horizontally Using PROC SQL

    Notice

    1. Understanding Joins

    (1) Inner join: Only the rows that match across all table(s)

    Inner join

    (2) Outer join: Rows that match across (as in the inner

    join) plus nonmatching rows from one or more tables.

    Left join

    Right join

    Full join

     When variable in the On condition clauses?it is assigned a

    dot rather than a backspace?and other variables is assigned a backspace rather than a dot.

    2. Understanding the Advantage of PROC SQL Joins.

    (1) PROC SQL joins do not require sorted or indexed tables. (2) PROC SQL joins do not require that the columns in join

    expressions have the same name.

    (3) PROC SQL joins can use comparison operators other

    than the equal sign (=).

    3. Using In-Line View

    (1) An in-line view is a nested query that is specified in the

    outer query’s FROM clause, Compared with a

    subquery, which is a nested query that is specified in a

    WHERE clause. Unlike a table, an in-line view exists only

    during query execution. Because it is temporary, an in-

    line view can be referenced only in the query in which it

    is defined. In addition, an in=line view can be assigned

    an alias but not a permanent name.

    (2) Caution: Unlike other queries, an in-line cannot contain

    an ORDER BY clauses.

    (3) The potential Advantage to using an in-line view rather

    than PROC SQL query:

    1. The complexity of the code is usually reduced, so

    that the code is easier to write and understand.

    2. In some cases, PROC SQL might be able to process

    the code more efficiently.

Report this document

For any questions or suggestions please email
cust-service@docsford.com