BookmarkSubscribeRSS Feed
sdknkn
SAS Employee

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

 

3 REPLIES 3
andypandy_swe
Obsidian | Level 7

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
Obsidian | Level 7

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
Obsidian | Level 7

Can you give some code examples?

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!

Discussion stats
  • 3 replies
  • 3086 views
  • 15 likes
  • 3 in conversation