I prefer the Data Step over PROC SQL. However, some applications are solved with much simpler code in PROC SQL. In those cases, I have to force myself on to the SQL side. Such a situation arises in the blog post Select By Group Conditional On Observation. The Data Step gives me more control than the SQL Procedure. I know exactly how the program compiles and executes and in what order. In PROC SQL, I tell SAS what to do, not how to do it. This results in less control. However, the undocumented METHOD Option lets you look under the procedure hood and see what operations PROC SQL performs during execution.

A _METHOD Example

Let us look at a very simple example to begin with. We write a simple SQL query creating an exact copy of the SASHELP.CLASS SAS data set. The log is posted right below the code.

proc sql _METHOD;
   create table class as
   select * from sashelp.class;
quit;

The log writes the description codes of the performed operations to the log. Two identified operations are written to the log. SQXCRTA and SQXSRC. These indicate that the SAS SQL Procedure creates a data set and that it retrieves observations from another data set to fill it.

These operations may seem obvious. And they are. They do not seem to add much information to our code. However, for more complicated SQL Procedure steps, the METHOD Option can provide crucial information to optimizing your SAS code. I will show an example you how the option can help you make code decisions in my next post.

Summary

The _METHOD Option is useful when you want to look under the hood and see what operations the SAS SQL Procedure actually performs. This gives you valuable information and helps you optimize your code.

This is just a brief example introduction to the option. For a more thorough walkthrough, see the article Exploring the PROC SQL _METHOD Option by Kirk Paul Lafler. Also, see the blog post Create Cartesian Products in SAS.

You can download the entire program from this post here.