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
- 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.
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. |