Juletip #10 Proc SQL and automatic macro variable

[sdknkn]

The Juletip of today is simple but useful. When using PROC SQL it is often handy to export the number of observations of the resulting table into a macro variable. The good thing is that SAS does this automatically. The name of this macro variable is “sqlobs”:

Look at this small sample code:

proc sql noprint;
create table extract as
select
*
from
sashelp.class
where sex='M'
;
quit;

%put NOTE: No. of males in sashelp.class: &sqlobs;

The resulting log:

1
2    proc sql noprint;
3    create table extract as
4    select
5    *
6    from
7    sashelp.class
8    where sex='M'
9    ;
NOTE: Table WORK.EXTRACT created, with 10 rows and 5 columns.

10   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.06 seconds
      cpu time            0.00 seconds


11
12   %put NOTE: No. of males in sashelp.class: &sqlobs;
NOTE: No. of males in sashelp.class: 10

Merry Christmas and a Happy New Year!

Niels-Kenneth Nielsen

[andypandy_swe]

You need to be a bit careful though... If you use &sqlobs efter a proc sql that doesn't create a table and uses the noprint option you get &sqlobs=1 every time, as I discovered a few years back when trying to find an error in my code... Note sure if this is a bug or a feature 🙂

proc sql noprint;
create table extract as
select *
from sashelp.class
where sex='M';
quit;
%put &=sqlobs; /*prints SQLOBS=10*/

proc sql;
select *
from sashelp.class
where sex='M';
quit;

%put &=sqlobs; /*prints SQLOBS=10*/

proc sql noprint;
select *
from sashelp.class
where sex='M';
quit;

%put &=sqlobs; /*prints SQLOBS=1*/

[jmic_nyk]

Well, the documentation doesn't cover all situations on this issue, in fact I think it's a little missleading.

Have just spent about 2h figuring this out - and to me it looks like the macrovar SQLOBS delivers value 1 if PROC SQL is able to fill in a value in a (new) macrovar. (eg finds a value from a table), but a 0 (zero) if it is not able to.

It is a little confusing because SQLOBS also, in some situations, can take the value <blank> (that is: no value).

[andypandy_swe]

Can you give some code examples?