Interact, learn and grow

Join Now
Reply
This is an open group. Sign in and click the "Join Group" button to become a group member and start posting.
Highlighted
SAS Employee
Posts: 2
Juletip #10 Proc SQL and automatic macro variable

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

 

Occasional Contributor
Posts: 5
Re: Juletip #10 Proc SQL and automatic macro variable

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*/

Contributor
Posts: 26
Re: Juletip #10 Proc SQL and automatic macro variable
[ Edited ]
Posted in reply to andypandy_swe

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

Occasional Contributor
Posts: 5
Re: Juletip #10 Proc SQL and automatic macro variable

Can you give some code examples?