The Essential PROC SQL Handbook for SAS Users and PROC SQL: Beyond the Basics Using SAS, Second Edition are also available for more assistance.
Howard Schreier's book explains and illustrates the use of PROC SQL in the context of the SAS DATA step and other SAS procedures (such as SORT, FREQ, MEANS/SUMMARY, APPEND, DATASETS, and TRANSPOSE) whose functionality overlaps and complements that of SQL. Using a side-by-side approach, this concise reference guide includes many extensively explained examples showing equivalent DATA step and SQL code. It enables SAS users to take advantage of existing SAS skills and knowledge while learning about SQL. Discussions cover the differences between SQL and the DATA step as well as situations where SQL and the DATA step are used together to benefit from the strengths of each.
Read the first set of about this book.
|33||*||The CREATE TABLE statement should be preceded by a PROC SQL statement and followed by a QUIT statement.|
|41||*||The RUN statement which appears in lower case should be in upper case.|
|62||***||The first SELECT statement should be preceded by a PROC SQL statement and followed by a QUIT statement.|
|92||***||The hypothetical CREATE TABLE statement should be preceded by a PROC SQL statement and followed by a QUIT statement.|
|99||**||There are obviously three statements in the code. The first sentence of the paragraph which follows should read: Before we look at the effect of this step (which comprises, apart from the PROC and QUIT statements, a single statement terminated by a single semicolon), let’s look at the syntax and compare it to that of the join.|
|133, 135, 137, 139||**||The running head (chapter indicator preceding the page number) is incorrect.|
|135||***||In each of the two SELECT statements, whitespace is needed between the FROM and GROUP BY clauses.
FROM twelves GROUP BY sex
|135||**||There should be a blank TITLE statement (TITLE;) after the last SELECT statement. Without it, the last declared title will persist.|
|147||**||There should be blank TITLE and FOOTNOTE statements (TITLE; FOOTNOTE;) after the SELECT statement. Without them, the last declared title and footnote will persist.|
|150||**||The next-to-last %PUT statement is superfluous.|
|154, 164, 173||*||The RUN statements in the PROC DATASETS steps should be indented.|
|163||*||In the DATA step, several tokens should be in upper case, as in
DATA simple; DO Measure = 1 TO 3; OUTPUT; END; RUN;
|164, 173, 190||**||The PROC DATASETS steps should be terminated with QUIT statements.|
|176||***||Each of the two INSERT statements should be preceded by a PROC SQL statement and followed by a QUIT statement.|
|215||*||The RUN statement which appears in lower case should be in upper case.|
|224||***||The SQL code should be preceded by a PROC SQL statement and followed by a QUIT statement.|
|228||*||To align tokens in the code, one space should be removed before the slash in the BREAK statement.|
|252||***||The CREATE TABLE statement must be followed by a QUIT statement. The statements must be submitted together in order to get accurate timings.|
Severity is reflected in the number of asterisks. One asterisk (*) indicates a minor issue such as a spelling or grammar mistake, or a deviation from style. Two asterisks (**) indicate ambiguity or other source of possible confusion. Three asterisks(***) indicate an incorrect statement (or code which produces incorrect results) where the mistake is fairly obvious. Four asterisks (****) indicate a subtle mistake.
Q: Some of the examples deliberately trigger ERROR conditions. How does one prevent those from "snowballing" and interfering with subsequent examples.
OPTIONS NOSYNTAXCHECK OBS=MAX;
Q: Is there a second form of CASE, parallel to SELECT (expression); etc.?
CASE WHEN age=11 THEN 'Zoo' WHEN age=12 THEN 'Museum' ELSE '[None]' END
CASE age WHEN 11 THEN 'Zoo' WHEN 12 THEN 'Museum' ELSE '[None]' END
Q: What happens when PROC PRINT or a DATA step is fed zero observations?
PROC PRINT DATA=preteen; WHERE age=10; RUN;
NOTE: No observations were selected from data set WORK.PRETEEN.
DATA tens; SET preteen; WHERE age=10; RUN;
Q: Is this usage of DISTINCT (within a function call) allowed in clauses other than SELECT?
PROC SQL; SELECT sex FROM sashelp.class GROUP BY sex HAVING MEAN(DISTINCT age) GT 13; QUIT ;
Names less cryptic than u1 and m1 would make things easier to follow.
Q: The documentation says that NOT can be coded directly before EXISTS or IN to negate the result of the comparison. Are there similar constructs for the other subquery contexts (ANY, ALL)?
SELECT name FROM sashelp.class WHERE SUBSTR(name,1,1) EQ ANY (SELECT sex FROM sashelp.class);
SELECT name FROM sashelp.class WHERE SUBSTR(name,1,1) NE ANY (SELECT sex FROM sashelp.class);
SELECT name FROM sashelp.class WHERE NOT SUBSTR(name,1,1) EQ ANY (SELECT sex FROM sashelp.class);
Q: Where are ANY and ALL documented? They don't appear in the SQL Procedure Component Dictionary.
Q: Exactly how can the missing values be replaced with zeroes?
PROC SQL; SELECT fname, age, COALESCE( ( SELECT COUNT(*) FROM classgirls WHERE moregirls.age = classgirls.age AND moregirls.fname ^= classgirls.fname GROUP BY classgirls.age ) , 0 ) AS SameAge FROM moregirls ; QUIT;
The same table name (UNIONALL) was used in an earlier example. Elsewhere, SQL and DATA step examples use the same names for their output. It would be better to have distinct names for all these tables.
pp. 8, 154, 157, 212
Q: The cited section on "Concepts" appears to be nonexistent. Where is the content?
Q: How can PROC SQL drop the table when PROC DATASETS has just done exactly that?
DATA demo; RUN; PROC DATASETS; DELETE demo; RUN; QUIT;
DATA demo; RUN; PROC SQL; DROP TABLE demo; QUIT;
Q: Why is the integrity constraint violation an ERROR in SQL but just a NOTE when the same thing is attempted in a DATA step?
PROC SQL; INSERT INTO demolib.fifteenups SET fname = "Newbie" SET fname = "Ronald" SET fname = "Lastly" ; QUIT;
ERROR: Add/Update failed for data set DEMOLIB.FIFTEENUPS because data value(s) do not comply with integrity constraint norepeats. NOTE: Deleting the successful inserts before error noted above to restore table to a consistent state.
DATA demolib.fifteenups; fname = 'Newbie'; OUTPUT; fname = 'Ronald'; OUTPUT; fname = 'Lastly'; OUTPUT; STOP; MODIFY demolib.fifteenups; RUN;
NOTE: The data set DEMOLIB.FIFTEENUPS has been updated. There were 0 observations rewritten, 2 observations added and 0 observations deleted. NOTE: There were 0 rejected updates, 1 rejected adds, and 0 rejected deletes.
Q: Shouldn't the SET statement in the second DATA step on the page be SET GDS_Demo? Similarly, shouldn't the second CREATE TABLE on p. 202 be FROM GDS_Demo?
The file names in the ODS HTML statements appear to be specific to Windows. Also, the code won't work unless &PATH can be resolved.
Q: Why is the code to create ARRIVALS and DEPARTURES not shown?
DATA Arrivals; INPUT Name $ Arrival : TIME5.; FORMAT Arrival TIME5.; CARDS; John 14:30 Paul 15:00 Ringo 15:30 George 16:00 ; DATA Departures; INPUT Name $ Departure : TIME5.; FORMAT Departure TIME5.; CARDS; John 15:30 Paul 16:00 Ringo 17:30 George 18:00 ;
Q: Why create the macro variable and include it in a %PUT statement when the outcome is not shown? Also, where did the the column heading in the output originate?
PROC SQL; SELECT COUNT(*) LABEL = 'Occurrences of 654321' FROM myRandoms WHERE myRandom = 654321 ; QUIT;
Occurrences of 654321 ----------- 22
There are a lot of page numbers, in the boxes, which don't refer to pages in this book, but rather to pages in the various SAS manuals. It's confusing.
The V. 9.2 doc includes more choices for object-item, specifically
*table-name.* specifies all columns in the PROC SQL table that is specified in table-name. *table-alias.* specifies all columns in the PROC SQL table that has the alias that is specified in table-alias. *view-name.* specifies all columns in the SAS view that is specified in view-name. *view-alias.* specifies all columns in the SAS view that has the alias that is specified in view-alias.
This article was originally published by Howard Schreier on sasCommunity.org
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.