CODEWITHSUNDEEP
sasproc-sql
JustinJDavies
JustinJDavies

Reputation: 2693

SAS PROC SQL difference in what SAS does

What is the difference in terms of what I'm asking the SAS engine to execute between these three code samples?

SAMPLE A

PROC SQL; CREATE TABLE foo AS 
SELECT
    aaa
FROM
   table1
;

CREATE TABLE bar AS 
SELECT
    aaa
FROM
   foo
;

SAMPLE B

PROC SQL; CREATE TABLE foo AS 
SELECT
    aaa
FROM
   table1
;

PROC SQL; CREATE TABLE bar AS                        /* Difference is here */
SELECT
    aaa
FROM
   foo
;

SAMPLE C

PROC SQL; CREATE TABLE foo AS 
SELECT
    aaa
FROM
   table1
;

QUIT;                                                /* Difference is here */

PROC SQL; CREATE TABLE bar AS 
SELECT
    aaa
FROM
   foo
;

Upvotes: 1

Views: 576

Answers (2)

Robert Penridge
Robert Penridge

Reputation: 8513

Any PROC statement that ends with a QUIT allows you to run multiple 'commands' from inside it. Say you're doing data exploration in SQL, you could enter your PROC SQL statement, and submit and execute your SQL commands 1 by 1 without having to issue a PROC SQL each time. When you're done you finally issue QUIT.

This was designed more for those people that work in SAS from a command line interface than for those that work in a GUI.

Another example would be the PROC DATASETS statement.

SAS will automatically recognize the end of a PROC SQL statement and automatically submit the QUIT; or RUN; code for you if it encounters certain other statement boundaries, like the beginning of another PROC. However, it's good programming practice to always explicitly end your PROC statements with the appropriate RUN; or QUIT; statement as it improves readability of your code.

Personally with PROC SQL I prefer to add the noprint option (PROC SQL noprint;) so that it doesn't generate output to the results window. If I want to see the results, I'll view the table using the explorer as I find that better suited to the task and it keeps my results window cleaner. I also like to issue no more than 1 statement within each PROC SQL so that I can run them individually and see their performance.

Upvotes: 1

DTS
DTS

Reputation: 423

Proc SQL treats each section that ends with a semi-colon (;)as a statement, and will continue to do so until you submit quit ;. Apart from the lack of a quit statement at the end, the first block is fine, as is the third. The second block has an extraneous Proc SQL, that just stops and starts the SQL submission, which isn't very good code. The first and third blocks are equally good for programming, the first will run slightly quicker than the third, but if the first part of the sql fails then the whole proc will fail, whereas the third block of code is slightly slower, but if the first proc sql fails, the second will still be run.

Upvotes: 2

Related Questions