"SAS ADVANCE" performing Queries Using PROC SQL

sql:structured Query Language

One, objectives in this chapter:

• Invoke the SQL procedure
• Select columns
• Define New columns
• Specify the table (s) to read
• Specify subsetting criteria
• Order rows by values of one or more columns
• Group results by values of one or more columns
• End the SQL procedure
• Summarize data
• Generate a report as the output of a query
• Create a table of the output of a query

B. What is the difference between PROC SQL?

• Unlike other PROC steps, PROC SQL is composed of many clauses. The most common scenario is that the proc SQL code contains two statements: the first quarter is proc SQL, the second quarter is the SELECT statement, and the SELECT statement contains more clauses, such as: Select ...,.. From ...,.... Weher and order by;
• PROC SQL procedures do not require a run statement, PROC SQL automatically executes each query;
• It is recommended to end the operation by adding the Quit statement at the end;

III. basic format of PROC SQL

Libname libref ' sas-data-library ';
1PROC SQL;                                             /*invoke the SQL procedure*/      
2    CREATE TABLE Table-name as                            

3     SELECT column-1<,...,column-N>                   /*Specifies the column (s) to be SELECTD*/4         from Table-1|View-1<,...,Table-N|View-N>      /*Specifies the table (s) to be queried*/5        <WHEREExpression>                            /*subsets the data based on a condition*/6        <GROUP  by column-1<,...column-N>>             /*classifies the data into groups based on the specifyed column*/7         <ORDER  by column-1<,...column-N>>;/*sorts the rows that the query returns by the values of the specified columns*/
8 QUIT;

"Precautions":

The SELECT statement contains more than one clause, ending with a semicolon

Iv. PROC SQL Output

PROC SQL statements	TYPE of Output
SELECT	Report
CREATE TABLE and SELECT	Table
CREATE VIEW and SELECT	PROC SQL VIEW

Five, programmatic considerations in the Order by statement

1) often placed at the end of the SELECT statement;
2) If the user needs to sort in reverse order, the keyword desc should be placed behind the variables in the order BY statement that need to be ordered in reverse order, for example:

1 Order  by desc;

3) in the ORDER BY clause, the user can substitute the variable name by specifying the position of the variable in the SELECT statement, for example:

1 proc SQL; 2      Select empid,jobcode,salary,salary*.  .  as Bonus 3       from Sasuser.payrollmaster 4      where salary<320005      orderby2 ;

4) in the order BY statement, the variables are listed by commas, and the multivariate sort

1  proc SQL; 2       Select empid,jobcode,salary,salary*.  .  as Bonus 3        from Sasuser.payrollmaster 4       where salary<320005       order by Jobcode, Empid;/order by 2,empid;

Six, join, or more tables

• If you need to join two or more tables,list the columns that is want to select from both tables in the SELECT clause.
• Seperate all column names with commas.
• If the variable name that the user needs to refer to appears in more than one table, it is necessary to precede the variable with the indicated prefix to specify its table name;
• If you need to connect multiple tables, you need to add more table names to the FROM clause and separate them with commas.

  1 proc SQL; 2     Select salcomp.empid, LastName, 3                newsals.salary,mewsalay 4      from Sasuser.salcomps, Sasuser.newsals 5     where salcomps.empid=newsals.empid6     orderby LastName;

Vii. summarizing Groups of Data

If you specify a GROUP by clause in a query that does not contain a summary function, your clause is changed to an ORDER B Y clause, and a message to the effect are written to the SAS log.

1 proc SQL; 2     Select  3               sum as totalmiles4       from  Sasuser.frequentflyer5     Group by MemberType; 6 /* outputs the milestraveled of each membertype and */

Viii. Having statement: Filter rows in proc SQL with a GROUP BY clause

A have clause works with the GROUP by clause to restrict the groups that is displayed in the output,based one or more s Pecified conditions.

1 procSQL;2     SelectJobcodeavg(Salary) as AVG3           fromSasuser.payrollmaster4          Group  byJobcode5           havingAvy (Salary)>400006          Order  byJobcode