University:
Boston University
Course:
GRS CS 910 | Directed Study: Systems
Academic year:

2022
Views:

105

Pages:

16
Author:

Whitney Galvan

PROC SQL VS. DATA STEP PROCESSING AGENDA • Comparison of DATA Step and PROC SQL capabilities • Joining SAS data using the DATA Step and PROC SQL C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . 1 COMPARISON OF DATA STEP AND PROC SQL CAPABILITIES C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . DATA STEP VS. PROC SQL • • CAPABILITIES COMPARISON DATA Step is typically sequential processing • Rows of data are processed in the same order they started in • Row 1, row 2, row 3, etc. • Output for most joins will have a consistent order • Can do random access using POINT= or KEY= options on SET statement PROC SQL uses an optimizer – dissimilar results • SQL has no concept of row order – does not process sequentially • Rows can be, and often are, returned in a random order unless an ORDER BY clause is used • GROUP BY does not include ORDER BY 2 DATA STEP VS. PROC SQL DATA Step • No issues as most processing for single tables as well as joins is sequential PROC SQL • Lags in performance without help • Always sort large tables before joining in SQL • NOT indexed! • Many times a series of 2-table joins will out-perform a multi-table (3+) join in PROC SQL C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . DATA STEP WHAT CAN I DO WITH IT? • Creating SAS data sets (SAS data files or SAS views) • Creating SAS data sets from input files that contain raw data (external files) • Creating new SAS data sets from existing ones by subsetting, merging, modifying, and updating existing SAS data sets • Analyzing, manipulating, or presenting your data • Computing the values for new variables • Report writing, or writing files to disk or tape • Retrieving information • File management C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . 3 PROC SQL • ANSI standard SQL is the base • SAS-isms added (functions, formats, labels, etc.) PROC SQL; SELECT <* | col1<, col2<... , coln>>> FROM << as > alias-name> ; QUIT; PROC SQL WHAT CAN I DO WITH IT? • Retrieve and manipulate data that is stored in tables or views. • Create tables, views, and indexes on columns in tables. • Create SAS macro variables that contain values from rows in a query's result. • Add or modify the data values in a table's columns or insert and delete rows. You can also modify the table itself by adding, modifying, or dropping columns. • Send DBMS-specific SQL statements to a database management system (DBMS) and retrieve DBMS data. 4 Capability DATA Step PROC SQL X X Creating SAS data sets (SAS data files or SAS views) Create Indexes on tables X Creating SAS data sets from input files that contain raw data (external files) X Analyzing, manipulating, or presenting your data X Writing external files to disk or tape X Computing the values for new variables X Retrieving system information X File management X Create SAS macro variables that contain values from rows in a query's result X X (listing reports) X X Send DBMS-specific SQL statements to a database management system (DBMS) and retrieve DBMS data Capability X DATA Step Use DO loops X Use Arrays X IF … THEN … ELSE processing X Use Object Oriented programming with JAVA or Hash objects X PROC SQL X 5 PROCESSING COMPARISONS C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . CONDITIONAL PROCESSING • CASE expression in SQL • IF THEN statement in the DATA step • Very flexible 6 PROC SQL CONDITIONAL PROCESSING: CASE EXPRESSION proc sql; select name, case when continent = ‘North America’ then ‘US’ when continent = ‘Oceania’ then ‘Pacific Islands’ else ‘None’ end as region from states; DATA STEP CONDITIONAL PROCESSING: IF THEN STATEMENT data new; set states; if continent = ‘North America’ then region =‘US’; else if continent = ‘Oceania’ then region = ‘Pacific Islands’; else region=‘None’; run; 7 INDEXES PROC SQL proc sql; drop index providerId from health.test; create unique index ProviderID on health.provider(providerID); PROC SQL can be used to create and administer indexes. CREATING MACRO PROC SQL VARIABLES proc sql noprint; select distinct style into :style01 - :style04 from work.houses; %put There were &sqlobs distinct values.; PARTIAL LOG: %put There were &sqlobs distinct values.; There were 4 distinct values. 8 CREATING MACRO PROC SQL VARIABLES proc sql noprint; select distinct style into :style01 from work.houses; %put There were &sqlobs distinct values.; PARTIAL LOG: %put There were &sqlobs distinct values.; There were 4 distinct values. DATA STEP CREATING MACRO VARIABLES data _null_; call symputx(' items ', ' text to assign’); call symputx(' x ', 123.456); run; Both the DATA step and SQL can create macro variables at execution time. The DATA step might be considered more flexible. 9 DATA STEP RETRIEVING SYSTEM INFORMATION System information can be retrieved by using DOPEN, DINFO and other related functions within the DATA step. DATA STEP RETRIEVING SYSTEM INFORMATION DOPEN opens a directory, and returns a directory identifier value. data diropts; length foption $ 12 charval $ 40; keep foption charval; rc=filename("mydir", "physical-name"); did = dopen("mydir"); numopts=doptnum(did); do i=1 to numopts; foption=doptname(did,i); charval=dinfo(did,foption); output; end; run; 10 DATA STEP RETRIEVING SYSTEM INFORMATION DOPTNUM returns the number of informational items that are available for a directory. data diropts; length foption $ 12 charval $ 40; keep foption charval; rc = filename("mydir", "physical-name"); did = dopen("mydir"); numopts = doptnum(did); do i = 1 to numopts; foption = doptname(did,i); charval = dinfo(did,foption); output; end; run; DATA STEP RETRIEVING SYSTEM INFORMATION DOPTNAME returns directory attribute information. data diropts; length foption $ 12 charval $ 40; keep foption charval; rc = filename("mydir", "physical-name"); did = dopen("mydir"); numopts=doptnum(did); do i = 1 to numopts; foption = doptname(did,i); charval = dinfo(did,foption); output; end; run; 11 DATA STEP RETRIEVING SYSTEM INFORMATION DINFO returns information about a directory. data diropts; length foption $ 12 charval $ 40; keep foption charval; rc = filename("mydir", "physical-name"); did = dopen("mydir"); numopts=doptnum(did); do i = 1 to numopts; foption = doptname(did,i); charval = dinfo(did,foption); output; end; run; DATA STEP RETRIEVING SYSTEM INFORMATION DCLOSE closes the open resource. data diropts; length foption $ 12 charval $ 40; keep foption charval; rc = filename("mydir", "physical-name"); did = dopen("mydir"); numopts=doptnum(did); do i = 1 to numopts; foption = doptname(did,i); charval = dinfo(did,foption); output; end; rc = dclose(did); run; 12 DATA STEP RETRIEVING SYSTEM INFORMATION There are similar functions for dealing with files and members. For more information, see SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition • FEXIST • FOPEN • FCLOSE • MOPEN • Etc. LOOPING IN THE DATA STEP The DATA step allows the following capabilities for iterative processing: • DO WHILE • DO UNTIL • Iterative DO 13 LOOPING IN THE DO WHILE DATA STEP DO WHILE The condition is evaluated at the top of the loop. Looping is terminated based on a condition. data test; do while(J lt 3); put J=; J+ +1; end; put 'do J: ' J=; run; Partial SAS Log: J=0 J=1 J=2 do J: J=3 LOOPING IN THE DO UNTIL DATA STEP DO UNTIL The UNTIL test is evaluated at the bottom of the loop. Looping is terminated based on a condition. data test; do until(K ge 3); put K=; K+ +1; end; put 'do K: ' K=; run; K=0 Partial SAS Log: K=1 K=2 do K: K=3 14 LOOPING IN THE ITERATIVE DO DATA STEP Iterative DO The iterative DO processes statements a specified number of times. data test; L = 0; do L = 1 to 2; put L=; end; put 'do L: ' L=; run; Partial SAS Log: L=1 L=2 do L: L=3 15 PROC SQL PRESENTING YOUR DATA PROC SQL has the capability to produce basic line-oriented reports, while the DATA step provides maximum flexibility for creating highly customized reports. Example SQL Output DATA STEP PRESENTING YOUR DATA The DATA step provides maximum flexibility for creating highly customized reports. Consider the following sample report: C op yr i g h t © 2 0 1 4 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d . 16

PROC SQL Vs. Data Step Processing

Related Documents

Recommended Documents

Description

Related Documents

Free up your schedule!

Take 5 seconds to unlock