How many records are in that PROC SQL result?

Did you know that PROC SQL captures the record count for a result set in a special automatic macro variable? When you create a subset of data to include in a report, it's a nice touch to add a record count and other summaries as an eye-catcher to the report title. I often see the following pattern in SAS programs, which adds an extra step to get a record count:

proc sql noprint;

create table result
as select * from sashelp.cars
where origin='Asia';

/* count the records in the result */
select count(model) into :resultcount
from result;
quit;

title "Summary of Cars from Asia: &resultcount. records";
proc means data=result;
run;

This creates a report with an informative title like this:

Here's the tip. Instead of including a SELECT INTO step that's going to make another pass through the data, you can rely on the &SQLOBS automatic macro variable. This variable holds the record "result set" count from the most recent SELECT clause.

proc sql noprint;
create table result
as select * from sashelp.cars
where origin='Asia';

%let resultcount=&sqlobs;
quit;

title "Summary of Cars from Asia: &resultcount. records";
proc means data=result;
run;

Because SAS replaces the value with each subsequent SELECT clause, it's important to assign it to another macro variable immediately if you intend to use it later. Here's the result:

Not only is this more efficient, but SAS automatically trims the whitespace from the SQLOBS variable so that it looks better in a TITLE statement. If you're using SELECT INTO to populate macro variables for other reasons, you can use the TRIMMED keyword to achieve the same effect.

proc sql noprint;

create table result
as select * from sashelp.cars
where origin='Asia';

%let resultcount=&sqlobs;

select avg(mpg_highway) into: AvgMpg TRIMMED
from result;

quit;

title "Summary of Cars from Asia: &resultcount. records, &AvgMpg. MPG Average";
proc means data=result;
run;

See also

See the SAS Help Center for more about how to use PROC SQL with the SAS macro language.

DATA step has a similar macro variable, SYSNOBS. It's a little bit trickier to use reliably though, since different interactions can affect the count. Test before you use!

SQLOBS is also handy for use in macro control loops.