Blogs

Blogs

How many records are in that PROC SQL result?

8
By Topics | Programming Tips

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

Tags PROC SQL
Share

About Author

Chris Hemedinger

Director, SAS User Engagement

+Chris Hemedinger is the Director of SAS User Engagement, which includes our SAS Communities and SAS User Groups. Since 1993, Chris has worked for SAS as an author, a software developer, an R&D manager and a consultant. Inexplicably, Chris is still coasting on the limited fame he earned as an author of SAS For Dummies. 

8 Comments

  1. Divyesh Dave on

    I had never heard of "sqlobs" as an automatic macro variable and I think it can definitely help in reducing number of lines of code in a program. As we know, COUNT(*) and COUNT( variable ) may not provide the same value. "sqlobs" can be a substitute for both COUNT(*) and COUNT( variable ). I did a small test as shown below.

    Thank you Chris for sharing this tip !

    data example;

    input num_value;
    datalines;
    1
    2
    .
    4
    ;

    /*Count will be 4*/
    proc sql;
    select count(*) from example;
    quit;

    proc sql;
    select * from example;
    quit;

    %put Value of sqlobs is &sqlobs;

  2. Ksharp on

    use keyword _NULL_ could save resource and faster. Borrowed it from Arthur Carpenter.

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

    %let resultcount=&sqlobs;
    quit;
    %put &sqlobs ;

    • Chris Hemedinger
      Chris Hemedinger on

      Great tip Ksharp! I'd guess this is optimized for SAS data sets especially, as the _null_ target is special for DATA step constructs too.

      • Paul Kent on

        not sure create table _null_ as select * from ... would be faster than

        select count(*) into :resultcount from ...

        I would suggest people try this both ways on some of their bigger tables.

  3. Michelle Buchecker on

    SQLOBS is also useful when creating a series of macro var. For example:

    proc sql noprint;
    select distinct model
    into :model1-
    from sashelp.cars;
    %let nummodels=&sqlobs;

    Then you can write a %DO loop to loop through &nummodels as necessary to process other data using &&model&i (assuming i is your do loop var name).

    This also employs the "trick" of creating a series of macro variables without knowing the ending number.

      • Paul Kent on

        Human Nature is a funny thing. I implemented it originally as :model1 - :model99999 so people could "specify" roughly how many there would be. So many people were worried that model9999 would cost way more than model999 when there were only 100 or so actual records. They would run a pre-query so they could choose the useful number of 9's !!

        model1 - model99999999 is practically the exact same thing as the open-ended variant added in 9.3

        but it makes people so much happier not to have to guess the upper bound! who knew :-)

        paul

Back to Top