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
%put NOTE: No. of males in sashelp.class: &sqlobs;
The resulting log:
2 proc sql noprint;
3 create table extract as
8 where sex='M'
NOTE: Table WORK.EXTRACT created, with 10 rows and 5 columns.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds
12 %put NOTE: No. of males in sashelp.class: &sqlobs;
NOTE: No. of males in sashelp.class: 10
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;
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).
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.