Chapter 8 Managing Processing Using PROC SQL Imelda

  • Slides: 14
Download presentation
Chapter 8 Managing Processing Using PROC SQL Imelda Go, John Grego, Jennifer Lasecki, 2011

Chapter 8 Managing Processing Using PROC SQL Imelda Go, John Grego, Jennifer Lasecki, 2011 1

PROC SQL OPTIONS INOBS= n – – Used to restrict the number of input

PROC SQL OPTIONS INOBS= n – – Used to restrict the number of input rows Useful for possibly large queries (e. g. , outer unions) proc sql inobs=5; select * from Ecoli outer union corr select * from Fcoli outer union corr select * from Entero; quit; Imelda Go, John Grego, Jennifer Lasecki, 2011 2

PROC SQL OPTIONS OUTOBS= n – – Used to restrict the number of output

PROC SQL OPTIONS OUTOBS= n – – Used to restrict the number of output rows OUTOBS= by itself restores the default (all rows output) Imelda Go, John Grego, Jennifer Lasecki, 2011 3

Example: OUTOBS proc sql outobs=5; select player, atbats from bbstats; quit; player atbats Christian

Example: OUTOBS proc sql outobs=5; select player, atbats from bbstats; quit; player atbats Christian Walker 271 Scott Wingo 240 Brady Thomas 231 Evan Marzilli 220 Robert Beary 211 Imelda Go, John Grego, Jennifer Lasecki, 2011 4

PROC SQL OPTIONS DOUBLE/NODOUBLE n – – specifies whether output is double spaced this

PROC SQL OPTIONS DOUBLE/NODOUBLE n – – specifies whether output is double spaced this option does not affect the appearance of HTML output proc sql outobs=5 double; select player, atbats from bbstats; quit; Imelda Go, John Grego, Jennifer Lasecki, 2011 5

PROC SQL OPTIONS NUMBER/NONUMBER n – – – Adds or removes row numbers from

PROC SQL OPTIONS NUMBER/NONUMBER n – – – Adds or removes row numbers from a table The column is labeled: Row Similar to OBS/NOOBS in PROC PRINT proc sql outobs=5 double number; select player, atbats from bbstats; quit; Imelda Go, John Grego, Jennifer Lasecki, 2011 6

PROC SQL OPTIONS FLOW | NOFLOW=n | FLOW=n m § – – Controls appearance

PROC SQL OPTIONS FLOW | NOFLOW=n | FLOW=n m § – – Controls appearance of wide character columns in listing output n sets the width of the flowed column Specifying n and m floats the width of the column between limits does not affect the appearance of HTML, PDF, or RTF output Imelda Go, John Grego, Jennifer Lasecki, 2011 7

PROC SQL OPTIONS § Example of FLOW proc sql flow = 10 15 double;

PROC SQL OPTIONS § Example of FLOW proc sql flow = 10 15 double; select winner. wscore label="Matching Score", winner. date format=worddate 18. label="Date", winner. wteam label="Team with Winning Score", loser. date format=worddate 18. label="Date", loser. lteam label="Team with Losing Score" from secscores as winner, secscores as loser where winner. wscore=loser. lscore; quit; Imelda Go, John Grego, Jennifer Lasecki, 2011 8

PROC SQL OPTIONS § § RESET can be used to change OUTOBS=, NUMBER, NODOUBLE,

PROC SQL OPTIONS § § RESET can be used to change OUTOBS=, NUMBER, NODOUBLE, NOFLOW, etc It can be inserted between SELECT clauses to restore options Imelda Go, John Grego, Jennifer Lasecki, 2011 9

PROC SQL OPTIONS § Example of RESET proc sql outobs=6 double; select player, atbats

PROC SQL OPTIONS § Example of RESET proc sql outobs=6 double; select player, atbats from bbstats; reset outobs= number nodouble; select player, hits from bbstats; quit; Imelda Go, John Grego, Jennifer Lasecki, 2011 10

Timing Information When learning about indexes in Chapter 6, we used the default timing

Timing Information When learning about indexes in Chapter 6, we used the default timing information available in the SAS Log § Timing info can be disaggregated by task using the STIMER option § Additional task info can be obtained using FULLSTIMER § Both methods are useful for benchmarking § Imelda Go, John Grego, Jennifer Lasecki, 2011 11

Timing Information If CPU Time is close to Real Time, than the system is

Timing Information If CPU Time is close to Real Time, than the system is operating efficiently § In addition to Real Time and CPU Time, FULLSTIMER provides data on § - Memory - Involuntary CPU time-slice releases - Page swaps Imelda Go, John Grego, Jennifer Lasecki, 2011 12

Dictionaries DICTIONARY. TABLES contains meta data about tables and views § DICTIONARY. COLUMNS contains

Dictionaries DICTIONARY. TABLES contains meta data about tables and views § DICTIONARY. COLUMNS contains meta data about columns in tables § Can be compared to PROC CONTENTS § Imelda Go, John Grego, Jennifer Lasecki, 2011 13

Dictionaries To find what variables are available, use DESCRIBE TABLE § A specific query

Dictionaries To find what variables are available, use DESCRIBE TABLE § A specific query proc sql; select memname, memtype, nobs, nvar, num_character, num_numeric, filesize, crdate from dictionary. tables where libname='WORK'; quit; § Imelda Go, John Grego, Jennifer Lasecki, 2011 14