"SAS ADVANCE" performing advanced Queries Using PROC SQL

First, objectives

• Display all rows, eliminate duplicate rows, and limit the number of rows displayed
• subset rows using other conditional operators and caculated values
• Enhance the formatting of query output
• Using summary functions, such as COUNT, with and without grouping
• Subset groups of data by using the HAVE clause
• Subset data by using Correlatedd and noncorrelated subqueries
• Validate query syntax.

Ii. syntax of the SELECT statement

1 SELECT column-1<,...,column-N>                       /*Select specifies the columns that the user needs to output*/2        from Table-1|View-1<,...,Table-N|View-N>       /*from the table or view specified to query*/3       <WHEREExpression>                             /*WHERE clause: Use an expression to subset or restrict the condition of a dataset*/4       <GROUP  by column-1<,...,column-N>>            /*Group by divides the dataset into groups by the columns specified later*/5       < havingExpression>/*HAVING clause: In group condition, the data after grouping with an expression subset or restrict*/6       <ORDER  by column-1<,...,column-N>>;/*ORDER BY: Sorts the results of a query based on subsequent variables*/

Remarks: The clauses in the PROC SQL SELECT statement need to be arranged in the order described above.

Three, Displaying all Columns

1. Use SELECT *: All columns can be rendered

1 proc SQL; 2      Select * 3       from Sasuser.staffchanges;

2. Feedback option (Debugging Tools: Lets the user clearly see what's being submitted to the SQL processor)

When you specify a SELECT * statement, the feedback option in PROC SQL outputs the expand list of columns (the detailed name of each column) in the log.

For example:

1 proc feedback; 2     Select*  3          from Sasuser.staffchanges;

the log will output:

202 proc SQL feedback; 203 Select * 204  from sasuser.staffchages;   to  :           Select  staffchanges. Empid,staffchanges. LastName, Staffchanges. Firstname,staffchanges. City, Staffchanges. State,staffchanges. PhoneNumber       from Sasuser. Staffchanges

Note: The log not only expands the asterisk (*) into a detailed list, but also resolves macro variables and places parentheses around expressions to show their order of Evalua tion.

Four, control the output line method

• Use the outobs= option to limit the number of Rows Displayed

      General form:PROC with outobs=option:      PROC SQL outobs = /* where n Specifies the number of rows for the output, where the outobs= option is similar to the obs= in the data set option */

Note: The outobs= option here only limits the number of rows displayed, but there is no limit on the number of rows to read. If the user needs to limit the number of rows read in, the inobs= option is available to control.

Example:

1 proc sql Outobs=ten; 2      Select flightnumber,date 3           from

Log hint: Waring:statement terminated early due to outobs=10 option.