How many records are in that PROC SQL result?

This post was kindly contributed by The SAS Dummy - go there to comment and to read the full post.

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

The post How many records are in that PROC SQL result? appeared first on The SAS Dummy.

This post was kindly contributed by The SAS Dummy - go there to comment and to read the full post.