Certification_-_SAS_Advanced_-_Summary_Note (2016-9-10)-- 这个写的很好!我看了这个.pdf

Certification_-_SAS_Advanced_-_Summary_Note (2016-9-10)-- 这个写的很好!我看了这个.pdf

This preview shows page 1 - 3 out of 55 pages.

1 PART 1 SQL Processing with SAS Chapter 1 Performing Queries Using PROC SQL PROC SQL Basics 1. The PROC SQL step does not require a RUN statement. PROC SQL executes each query automatically. If RUN statement is used, SAS ignores that. 2. PROC SQL continues to run after you submit a step. To end the procedure, one should submit another PROC step, DATA step or a QUIT statement. Writing a PROC SQL Step 1. Syntax PROC SQL; SELECT column- 1 <,… column -n> FROM table-1|view- 1 <,… table -n|view-n> <WHERE expression> <GROUP BY column- 1 <,… column -n>> <ORDER BY column- 1 <,… column -n>>; - GROUP BY: classifies the data into groups based on the specified columns - ORDER BY: sorts the rows that the query returns by the variables of the specified columns. - Notice: the order of clauses with a SELECT statement in PROC SQL should appear as shown above. 2. The SELECT Statement - SELECT and FROM are required clauses. - Example Selecting Columns 1. The column names in SELECT clause are separated by commas. 2. Creating New Columns - Using keyword AS, such as salary*0.06 as bonus - BONUS is the new column, it will be display in the output exactly in lower case. - If both a label alias and a column alias are specified for a new column, the label is displayed as the column heading in the output (P10) Specifying the Table - Example: select … from sasuser.payrollmaster Specifying Subsetting Criteria - WHERE clause in the SELECT statement - The columns specified in the WHERE clause do NOT have to be specified in the SELECT clause Ordering Rows - Using ORDER BY clause - By default, the ORDER BY clause sorts rows in ascending order. To sort rows in descending order, specify the keyword DESC following the column name. Such as order by jobcode desc; - Notice: the ORDER BY clause is ended with a semicolon because ORDER BY is the last clause in the SELECT statement - In the ORDER BY clause, to specify the column by the column’s position in the SELECT clause rather than by name: order by 2; Notice: 2 is the position of column JOBCODE in the SELECT clause. - Ordering by multiple columns, list multiple column names or numbers and use commas to separate them in the ORDER BY clause. Querying Multiple Tables
Image of page 1

Subscribe to view the full document.

2 proc sql; select salcomps.empid, lastname, newsals.salary, newsalary from sasuser.salcomps, sasuser.newsals where salcomps.empid=newsals.empid order by lastname; - Same-named columns should be specified by a table name as a prefix. Specifying a table-name prefix with a column that only exists in one table is syntactically acceptable. - Notice: Prefixing a table name to a column name is called qualifying the column name. - The SQL procedure does not attempt to convert data types Summarizing Groups of Data 1. GROUP BY clause 2. Example proc sql; select membertype, sum(milestraveled) as TotalMiles from sasuser.frequentflyers group by menbertype; - Notice: a GROUP BY clause without summary functions will be automatically changed to an ORDER BY clause (P18).
Image of page 2
Image of page 3
  • Fall '14

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern