DATA Step, Macro, Functions and more

Why use QUIT; with proc SQL?

Reply
Respected Advisor
Posts: 4,651

Why use QUIT; with proc SQL?

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. Smiley Happy But the question remains: is it ever necessary?

PG

PG
PROC Star
Posts: 7,363

Re: Why use QUIT; with proc SQL?

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.

SAS Super FREQ
Posts: 8,743

Re: Why use QUIT; with proc SQL?

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

Regular Contributor
Posts: 184

Re: Why use QUIT; with proc SQL?

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

SAS Super FREQ
Posts: 8,743

Re: Why use QUIT; with proc SQL?

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

Regular Contributor
Posts: 184

Re: Why use QUIT; with proc SQL?

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:

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

Regular Contributor
Posts: 241

Re: Why use QUIT; with proc SQL?

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;

Super User
Posts: 9,681

Re: Why use QUIT; with proc SQL?

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

Respected Advisor
Posts: 4,651

Re: Why use QUIT; with proc SQL?

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.

PG

PG
Ask a Question
Discussion stats
  • 8 replies
  • 9938 views
  • 1 like
  • 6 in conversation