Example: stock market

SUGI 23: Intermediate PROC SQL - SAS

1 Intermediate PROC SQLT homas J. Winn Jr., Texas State Comptroller's Office, Austin, TexasABSTRACTThis tutorial presentation will provide a practical explanationof some advanced features of the SAS SQL Procedure,including the use of: summary functions, subqueries,complex joins, in-line views, indexes, macro variables, andthe SQL Pass-Through Facility. Also included will be adiscussion of several useful tips for improving theperformance of PROC SQL queries. This paper is for SASprogrammers who already know how to code simpleSELECT .. FROM .. WHERE .. ORDER BY .. querystatements for PROC SQL, and who want to be able to presentation does not explain SAS/ACCESS concepts,nor the ACCESS, nor DBLOAD procedures.

2 A query is a request to retrieve some data from a database table or view. A query may be a simple question about the information which is …

Tags:

  Data, Corps, Intermediate, Sugi, Sugi 23, Intermediate proc sql

Information

Domain:

Source:

Link to this page:

Please notify us if you found a problem with this document:

Other abuse

Transcription of SUGI 23: Intermediate PROC SQL - SAS

1 1 Intermediate PROC SQLT homas J. Winn Jr., Texas State Comptroller's Office, Austin, TexasABSTRACTThis tutorial presentation will provide a practical explanationof some advanced features of the SAS SQL Procedure,including the use of: summary functions, subqueries,complex joins, in-line views, indexes, macro variables, andthe SQL Pass-Through Facility. Also included will be adiscussion of several useful tips for improving theperformance of PROC SQL queries. This paper is for SASprogrammers who already know how to code simpleSELECT .. FROM .. WHERE .. ORDER BY .. querystatements for PROC SQL, and who want to be able to presentation does not explain SAS/ACCESS concepts,nor the ACCESS, nor DBLOAD procedures.

2 SAS/ACCESS software provides interfaces for many popular illustrative examples used in this paper are based upona simplified, hypothetical personnel data base for a fictitiouscompany. It should not be identified with any data basewhich actually is used by the State of Texas, or any of itsagencies. The imaginary data base, PERSLIB (which couldbe a SAS data Library), contains numerous tables (possiblySAS data sets); however, we shall be concerned with onlyfour of them: DIVISION, EMPLOYEE, JOBV, and includes current and historical informationpertaining to the company s organizational structure.

3 Eachof the operational entities, which are called divisions, areidentified by their number and name, DIVNUM andDIVNAME, respectively. Each division has a lifespan whichbegins on its DIVBEGDT, and ends on its DIVENDDT. Thevalue of DIVENDDT would be null for current includes current and selected historicalinformation pertaining to company employees. There is onlyone row (observation) for each person ever employed by thecompany. It includes personal information, such asEMPNUM, and EMPNAME, RACE, SEX, BIRTHDAT, thenumber of years of formal education completed (EDUYRS),as well as the most recent home address information andhome telephone number.

4 It also contains certain itemswhich are of particular concern to the company: the date onwhich the employee was hired by the company(HIREDATE), the date on which the employee may haveterminated his/her employment with the company(TERMDATE), and the unique number associated with theemployee s most recent job assignment (POSITION).JOBV includes current and selected historical informationpertaining to POSITIONs (and not directly with EMPNUMs)which were established in each DIVISION. JOB includessuch information as job classification code (CLASSCD), jobcategory code (EMPLTYPE), and current salary amount(MOSALRY).

5 Each job position also has a life span, whichbegins on its POSBEGDT and ends on its contains current and historical informationregarding changes in job assignment or salary, associatedwith each employee ever employed by the company. Eachrecord includes EMPNUM, DIVNUM, POSITION,MOSALRY, and the effective date of the change, EFFDATE. A BRIEF REVIEW OF FUNDAMENTAL IDEASS tructured Query Language (SQL)SQL is a language that talks to a relational databasemanagement system. It is a standard. There are manyimplementations of SQL. Each RDBMS may use its ownparticular dialect of SAS SQL ProcedureSAS has an implementation of Structured Query Languagecalled PROC SQL.

6 PROC SQL follows most of theguidelines set by the American National Standards Institute(ANSI) in its implementation of SQL. PROC SQL includesseveral enhancements, which exceed the ANSI specifications, for greater compatibility with other elementsof the SAS SQL processes SQL statements that read andupdate tables. PROC SQL uses SQL to create, modify, andretrieve data from tables and views (and SAS data sets).PROC SQL can be used in batch programs or during aninteractive SAS session. PROC SQL can be used on SASfiles, flat files, VSAM files, database tables, andcombinations of these to do query operations.

7 PROC SQLalso can perform many ordinary data manipulation andreporting operations customarily accomplished using data step programming, and the PRINT, SORT, MEANS andSUMMARY procedures. (See the sugi 22 paper by Winn)Syntax for the SAS SQL ProcedureThe SQL Procedure includes several statements, not all ofwhich are always required. SQL itself is made up ofmodular components, and PROC SQL includes statementsand clauses which reflect those is the basic syntax:PROC SQL < option < option > ..ALTER alter-statement;CREATE create-statement; DELETE delete-statement;DESCRIBE VIEW view-name;DROP drop-statement; INSERT insert-statement;RESET < option < option >.

8 >;SELECT select-statement;UPDATE update-statement;VALIDATE query-expression;For a complete explanation of these components, pleaseconsult the PROC SQL reference manual (see references).Queries, Views, Joins, and Result SetsA view is a stored specification of a database request. Aview is a description of selected data from one table, or fromseveral tables. It may be helpful to regard a view as a TutorialsAdvanced TutorialsAdvanced Tutorials2A query is a request to retrieve some data from a databasetable or view. A query may be a simple question about theinformation which is in a single table, or it may be a complexquestion about information gathered from several most common way of combining data from severaltables is through a join operation.

9 Joins combineinformation from multiple tables by matching rows that havecommon values in key columns which relate the do not have to be sorted before they are result set is what you get back when you query adatabase table or view. A result set also is a PROC SQL Syntax for QueriesThe most frequently-encountered usage for PROC SQL is toprovide a query to one or more SAS data Files or SAS DataViews. This is accomplished by means of a basic form for the SELECT statement is:SELECT column-1, column-2, .. FROM table-a, table-b, .. WHERE expression ORDER BY column-r, column-s, .. ;The SELECT statement specifies the column-names in aparticular table (the FROM clause) from which the data areto be chosen, it further subsets these data according to acertain value contained in some of the rows (the WHERE clause), and then it identifies the column to be used as thebasis for re-sequencing the extracted data for the printedreport (the ORDER BY clause).

10 Here is an example of the use of the SELECT statement:PROC SQL ; SELECT , , , , , , , , , , , , , , , FROM AS A, AS B, AS C WHERE DIVNUM IN (14, 19) AND IS NULL AND AND AND IS NULL AND >= 01 SEP1997 D AND IS NULL ORDER BY DIVNUM, EMPNUM;(This query is an inner join which would returnselected information concerning all of the currentemployees in divisions 14 or 19 who are working incurrent job assignments which were establishedsince September 1, 1997.)


Related search queries