BookmarkSubscribeRSS Feed
PGStats
Opal | Level 21

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
11 REPLIES 11
art297
Opal | Level 21

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.

Cynthia_sas
SAS Super FREQ

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

Howles
Quartz | Level 8

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

Cynthia_sas
SAS Super FREQ

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

Howles
Quartz | Level 8

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

chang_y_chung_hotmail_com
Obsidian | Level 7

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;

DrAbhijeetSafai
Pyrite | Level 9

The detailed explanation is very helpful!

Dr. Abhijeet Safai
Associate Data Analyst
Actu-Real
Ksharp
Super User

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

PGStats
Opal | Level 21

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
AllanBowe
Barite | Level 11

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!  

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
Kurt_Bremser
Super User

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 27979 views
  • 7 likes
  • 9 in conversation