03 Jun

SAS: PROC SQL, A Few Options

PROC SQL has a handful of options which apparently we should know so I’m going to roll through them.

INOBS vs. OUTOBS

These limit your source data or your output.  One works when reading data and the other works when outputting data.  I’ll let you guess which one is which.  You use them as follows:

proc sql inobs=5 outobs=10;
	SELECT *
	mysql.admit;
quit;

                                                                   Act
ID    Name            Sex       Age      Date    Height    Weight  Level      Fee
---------------------------------------------------------------------------------
2458  Murray, W       M          27         1        72       168  HIGH     85.20
2462  Almers, C       F          34         3        66       152  HIGH    124.80
2501  Bonaventure, T  F          31        17        61       123  LOW     149.75
2523  Johnson, R      F          43        31        63       137  MOD     149.75
2539  LaMance, K      M          51         4        71       158  LOW     124.80

This is another of those award-winning examples that I give. This query would be the same, regardless of which one I set, however, it is useful, I think, to show that both can be used in a PROC SQL statement. This is, exactly, the kind of thing SAS will test for.

Here’s a more interesting example:

proc sql inobs=3 outobs=4;
	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex = 'M'
	UNION
	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex='F';
quit;

 Name                 Age  Sex
 -----------------------------
 Almers, C             34  F  
 Bonaventure, T        31  F  
 Johnson, R            43  F  
 Jones, M              29  M

What happened is that SAS read 3 rows from the two union statements, totaling 6 records, however because the output was limited to only 4 records that’s all you get. Just to prove that I’ll change the outobs to 6.

proc sql inobs=3 outobs=6;
	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex = 'M'
	UNION
	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex='F';
quit;

Name                 Age  Sex
-----------------------------
Almers, C             34  F  
Bonaventure, T        31  F  
Johnson, R            43  F  
Jones, M              29  M  
LaMance, K            51  M  
Murray, W             27  M

PROMPT vs. NOPROMPT

I'm looking for the code, that should be here, but isn't, because SAS flaked.

I’m looking for the code, that should be here, but isn’t.

If you add the PROMPT option to a PROC SQL statement, it should, because it doesn’t work for me, prompt you to verify that you are limiting the records. In other words, it will allow you to bypass the limits you specify.  The default, which worked really well on my system is NOPROMPT.

DOUBLE vs NODOUBLE

All this does is fluff up your output by double-spacing it.  The default is NODOUBLE and it only works in LIST output.

NUMBER vs NONUMBER

This is another of those exciting options.  It adds row numbers to your queries.  The default, is NONUMBER and just because I haven’t done an example in 100 words or so, here you go. Oh, and I gave you bonus content, free, see how generous I am, and added a DOUBLE to it.

proc sql outobs=5 double number;
	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex = 'M';
quit;

   Row  Name                 Age  Sex
-------------------------------------
     1  Murray, W             27  M  

     2  LaMance, K            51  M  

     3  Jones, M              29  M  

     4  King, E               35  M  

     5  Pitts, D              34  M

STIMER vs NOSTIMER

The default is NOSTIMER.  All this does is write query processing time to the log. It must be enabled as a system option.

It looks something like this,

NOTE: SQL Statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds

I tried, but was unable to, turn it off.

FLOW

FLOW is another one of those, or these, things that impact only list output. Here’s to the good examples that I’m not going to be able to provide.

Let’s look at each option.

NOFLOW, which is the default, means that if you generate a result, that is longer than the page width, that it overflows into the next line.

FLOW = x, means that a columns maximum width is x. In other words, if x=15, that a column will wrap when 15 characters are in a column.

FLOW = x y, means that a column will have a width between x and y. SAS, specifically uses the word, BETWEEN, so I don’t know if they really mean inclusive of x and y, or greater than and less than. For example, FLOW = 10, 15 means that a columns length will be between 10 and 15 characters.

FLOW, means, FLOW = 12 200.

Essentially FLOW is telling SAS to not flow into the next line, and what to do when that might happen.

I really need to get some samples for this but I probably won’t.

You can reset options by using the RESET command.  It works as follows:

proc sql double outobs=5;
	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex = 'F';

	reset outobs= nodouble;

	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex = 'F';
quit;

Name                 Age  Sex
-----------------------------
Almers, C             34  F  

Bonaventure, T        31  F  

Johnson, R            43  F  

Reberson, P           32  F  

Eberhardt, S          49  F  

Name                 Age  Sex
-----------------------------
Almers, C             34  F  
Bonaventure, T        31  F  
Johnson, R            43  F  
Reberson, P           32  F  
Eberhardt, S          49  F  
Nunnelly, A           44  F  
Oberon, M             28  F  
Quigley, M            40  F  
Takahashi, Y          43  F  
Ivan, H               22  F  
Wilcox, E             41  F

I almost skipped this one but I’m glad I didn’t. I never would have expected that you reset the outobs as outobs=. Of course, if you wanted to you could simply invoke a second PROC SQL statement, with the options you wish, but RESET allows you to do it within a single PROC SQL, which probably makes things run a bit faster and keeps the code a bit cleaner.

Finally, you can view “system” option settings with the PROC OPTIONS statement. Here’s a quick example. Note the word system.

proc options option=number value;
run;

In the log,

Option Value Information For SAS Option NUMBER
    Option Value: NUMBER
    Option Scope: Default 
    How option value set:  Shipped Default

If you don’t include the values keyword this is all you get,

proc options option=number;
run;

 NUMBER            Print page number on each page of SAS output

I think two things. One, this post went longer than I had planned. I say that because I know anyone reading something like this wants to hear me bitch about the writing I’m doing. Two, I think I’m just about done with PROC SQL.

One thought on “SAS: PROC SQL, A Few Options

Leave a Reply

Your email address will not be published. Required fields are marked *