topic Re: Why use QUIT; with proc SQL? in SAS Programming

Why use QUIT; with proc SQL?

PGStats — Sun, 19 Aug 2012 00:42:06 GMT

Hi all,

Data steps and most SAS procedures require many statements terminated by a RUN; before they can run. Proc SQL is different. Every statement is complete and can be executed immediately, without knowing what follows. So I wonder, what is the role of the QUIT; statement? Does it ever make a difference, besides writing the true running time to the log?

I never use to bother with the QUIT; statement. Now I tend to use it for neatness and for avoiding being scolded. But the question remains: is it ever necessary?

Re: Why use QUIT; with proc SQL?

art297 — Sun, 19 Aug 2012 01:26:57 GMT

PG: Of course you are referring to your request for help regarding https://communities.sas.com/message/135639#135639

In responding, I ran your code and got the same error that the OP mentioned. I looked at your code and couldn't find anything wrong, other than my own preference to end all procs and steps with either quit;, run;, or both.

I added quit; statements to the proc sql and everything ran.

However, honestly, I just re-copied your code to a new sas session and it ran perfectly.

Thus, call me confused for now.

Re: Why use QUIT; with proc SQL?

Cynthia_sas — Sun, 19 Aug 2012 16:09:23 GMT

Hi:

SAS procedure steps terminate when one of these is encountered:

1) the beginning of the "next" DATA or PROC step

2) an explicit step boundary is reached (either RUN; or QUIT; as appropriate)

3) you are submitting a job in "batch" mode (such as in a SYSIN DD * on the mainframe) and the end of the input file is encountered (which would be */ on the mainframe). For example, you might have code in EG without ANY step boundaries, such as this:

proc sql;

select *

from sashelp.class

order by age;

And the code in EG, when submitted, will produce results. There is NOTHING else in the file. No RUN; no QUIT; -- This might lead you to believe that RUN; or QUIT; is optional -- and the reason it is (sort of) optional for EG is that EG code is submitted in "batch" mode to the server, and in the wrapper code that EG adds to every submission is this:

30 QUIT; RUN;

Notice that EG adds a QUIT; and RUN; at the "bottom" of every submission of code to the server.

So, EG is protecting you from not having a final step boundary. If you try to run the above code in DISPLAY MANAGER, on the other hand, your SQL step will not execute and you will see the "PROC SQL RUNNING" message at the top of your window. If you try to get out of things by submitting just RUN; then you will see the following message in the log:

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.

When you finally submit a QUIT; in Display Manager or another step (like a PROC PRINT or PROC whatever), then that next PROC step causes the PROC SQL step to terminate and the "PROC SQL RUNNING" message will go away. So if you submitted the above code two or three times, you would get output, because the second PROC SQL would terminate the first PROC SQL, but then the second PROC SQL would still be unterminated and you would still see "PROC SQL RUNNING" in your window.

Another category of procedures that need QUIT; as the step boundary are the SAS/GRAPH procedures: GCHART, GPLOT, etc. Some of the STAT procedures end with QUIT;

The trouble with putting an SQL step or any unterminated PROC or DATA step into a macro program without a terminating QUIT; or RUN; as the step boundary is that you want your macro program to generate completely working code that will finish running all the steps before the next iteration of generated code hits the compiler and starts execution. It keeps your log messages cleaner and clearer and it simplifies debugging.

As a best practice (and for neatness), I recommend ending every step with the appropriate step boundary (either a RUN; or QUIT; as documented for your procedure of choice) If you want to, you can follow the EG model and use both.

cynthia

Re: Why use QUIT; with proc SQL?

Howles — Mon, 20 Aug 2012 02:10:59 GMT

SQL and/or global statements following the PROC SQL statement will be processed before PROC SQL goes into its waiting mode.

If you try to run the above [unterminated] code in DISPLAY MANAGER, on the other hand, your SQL step will not execute

Re: Why use QUIT; with proc SQL?

Cynthia_sas — Mon, 20 Aug 2012 02:22:14 GMT

Yes, I misspoke. I meant to type "will appear not to execute" -- because you do not see any output from the step until the next step boundary happens. And if you keep submitting, over and over again, you will likely always have output that seems behind by 1 submission. Very frustrating.

cynthia

Re: Why use QUIT; with proc SQL?

Ksharp — Mon, 20 Aug 2012 05:07:46 GMT

A same activity as SQL is proc datasets which also need to use QUIT to end it . You can keep submitting statements you want before QUIT ,sas will not end proc sql before quit which can save some resource . QUIT means end the run group .e.x.

proc datasets libaray=work nolist;

modify class;

rename sex=f_m ;

run;

repair class;

run;

index create sex ;

run;

quit; <-- end proc datasets

Ksharp

Re: Why use QUIT; with proc SQL?

PGStats — Mon, 20 Aug 2012 13:34:43 GMT

Yes, some STAT procedures also support run groups, but proc SQL executes every statement as soon as it's submitted. That's like having an implicit run statement after each query.

What I take away from this discussion is that the QUIT statement is good practice but not essential in SQL.

Re: Why use QUIT; with proc SQL?

Howles — Mon, 20 Aug 2012 15:59:04 GMT

It is true that some SQL statements will process without placing any messages in the log, But output (ODS or a CREATEd table or view) happens right away, before SQL waits for the next statement.

select * from sashelp.class ;

create view myview as select * from sashelp.class ;

select * from sashelp.class where age LT 11 ;

Cynthia@sas wrote:

cynthia

Re: Why use QUIT; with proc SQL?

chang_y_chung_hotmail_com — Mon, 20 Aug 2012 18:50:08 GMT

Interesting!

So OP would rather put:

(nothing)

But OP's boss (or the one who is scolding OP) wants OP to put:

QUIT;

And the Enterprise Guide puts (twice, in fact):
QUIT; RUN;

Finally, Vince puts the following at the end of every PROC (including PROC PRINT):

RUN; QUIT;

Re: Why use QUIT; with proc SQL?

AllanBowe — Sun, 15 Dec 2019 20:20:45 GMT

On the contrary, I'd say the use of the `quit;` statement in `proc sql` is BAD practice as it encourages users to reinstantiate the procedure across multiple SQL statements.

I see only one good reason to `quit;` and that is if you want to control the timing statistics.

quit quitting!

Re: Why use QUIT; with proc SQL?

DrAbhijeetSafai — Wed, 03 Aug 2022 08:25:04 GMT

The detailed explanation is very helpful!

Re: Why use QUIT; with proc SQL?

Kurt_Bremser — Wed, 03 Aug 2022 08:57:06 GMT

@AllanBowe wrote:

I see only one good reason to `quit;` and that is if you want to control the timing statistics.

This is the exact reason why I always QUIT. I want to see the stats of every single operation in SQL. Especially helpful when debugging/optimizing from the logs of batch jobs.

Re: Why use QUIT; with proc SQL?

ANON4 — Mon, 06 May 2024 11:26:20 GMT

I ran into a situation where the QUIT statement is important in PROC SQL. I am running SAS 9.4 on Enterprise Guide 8.3, Update 8.

For example, if you run the two code blocks below you will get this warning message: WARNING: Output 'nlevels' was not created. Make sure that the output object name, label, or path is spelled correctly. Also, verify that the appropriate procedure options are used to produce the requested output object. For example, verify that the NOPRINT option is not used.

proc sql;

create table CLASS AS

select *

from sashelp.class;

ods select none;

ods output nlevels=temp;

proc freq data=class nlevels;

tables _all_;

run;

If you run the same two code blocks separately, you will not get a warning message. Like Cynthia wrote above, a SAS procedure step will terminate when the beginning of the "next" DATA or PROC step is encountered. With that in mind, I inserted a data _null_ code block like below and ran all three code blocks together and I did not receive a warning message.

proc sql;

create table CLASS AS

select *

from sashelp.class;

data _null_;

x=1;

run;

ods select none;

ods output nlevels=temp;

proc freq data=class nlevels;

tables _all_;

run;

This finally led me to the solution - add QUIT at the end of the first code block. The code below runs fine:

proc sql;

create table CLASS AS

select *

from sashelp.class;

quit;

ods select none;

ods output nlevels=temp;

proc freq data=class nlevels;

tables _all_;

run;

This may have been obvious to some SAS veterans but it took me forever and a day to figure out. I am posting what I learned in case anyone else runs into a similar situation.

Re: Why use QUIT; with proc SQL?

WarrenKuhfeld — Mon, 06 May 2024 11:55:49 GMT

You might find this discussion helpful even though it does not explicitly mention SQL.

https://documentation.sas.com/doc/en/pgmsascdc/v_018/statug/statug_ods_examples06.htm

Re: Why use QUIT; with proc SQL?

Rick_SAS — Mon, 06 May 2024 13:20:25 GMT

Short answer: Use QUIT to terminate interactive procedures. Reasons include both clarity and to ensure that ODS output (and other global SAS statements) are correctly applied. See this blog post from 2011: https://blogs.sas.com/content/iml/2011/12/12/sas-tip-put-ods-statements-inside-procedures.html