topic Re: How to see how many observations were produced by PROC SQL? in SAS Programming

How to see how many observations were produced by PROC SQL?

Nietzsche — Mon, 06 Feb 2023 06:49:07 GMT

Hello,

Quick question, is there a quick way to find out how many observations in the proc sql result?

eg.

proc sql;
	 select *
	 from sashelp.fish
	 where species="Pike";

Re: How to see how many observations were produced by PROC SQL?

Astounding — Mon, 06 Feb 2023 06:52:06 GMT

Yes, there is an automatically produced macro variable called &sqlobs

Re: How to see how many observations were produced by PROC SQL?

Nietzsche — Mon, 06 Feb 2023 08:35:12 GMT

@Astounding wrote:
Yes, there is an automatically produced macro variable called &sqlobs

um.. how do I print/display a macro variable value?

I have learnt about macro variable in Base exam, but only learnt about macro variable as a way to recall a value in another proc step (such as proc print).

edit:

I followed this guide

and tried this code.

proc sql;
	 select *
	 from sashelp.fish
	 where species="Pike";
quit;

%put &sqlobs;

not getting any obs count in the log.

Re: How to see how many observations were produced by PROC SQL?

FreelanceReinh — Mon, 06 Feb 2023 09:04:26 GMT

@Nietzsche wrote:

not getting any obs count in the log.

You did. It's the "17" between line numbers 79 and 80.

Often it helps to write the macro variable name to the log, too, which can be done by inserting an equals sign after the ampersand:

%put &=sqlobs;

Log:

SQLOBS=17

Re: How to see how many observations were produced by PROC SQL?

Nietzsche — Mon, 06 Feb 2023 09:57:26 GMT

thank you. It is very hard to see in the log.

I am trying to find a way to display the sqlobs number on the result page, with something like the Title statement.

Title &sqlobs;

proc sql;
	 select *
	 from sashelp.fish
	 where species = 'Pike';
quit;

But the SQLOBS count printed is always the count of the previous run, not the count of the current run.

Is there a way for display the count in the title with the current run SQLOBS value?

Re: How to see how many observations were produced by PROC SQL?

FreelanceReinh — Mon, 06 Feb 2023 10:16:19 GMT

Usually, you would write the result of the PROC SQL query to a dataset (using the CREATE TABLE statement), say, WANT. Then &SQLOBS is available and can be used in a TITLE statement followed by a reporting procedure (PROC PRINT, PROC REPORT, etc.) using WANT as the input dataset. Otherwise, the PROC SQL step comes too late to populate the SQLOBS macro variable after the TITLE statement was already executed.

Re: How to see how many observations were produced by PROC SQL?

Ksharp — Mon, 06 Feb 2023 12:25:50 GMT

proc sql number;
	 select *
	 from sashelp.fish
	 where species="Pike";
quit;

Re: How to see how many observations were produced by PROC SQL?

PaigeMiller — Mon, 06 Feb 2023 13:12:08 GMT

@Nietzsche wrote:

thank you. It is very hard to see in the log.

I am trying to find a way to display the sqlobs number on the result page, with something like the Title statement.
Title &sqlobs;

proc sql;
	 select *
	 from sashelp.fish
	 where species = 'Pike';
quit;
But the SQLOBS count printed is always the count of the previous run, not the count of the current run.

Is there a way for display the count in the title with the current run SQLOBS value?

If the number is hard to see, there are a number of ways to make it easier to see. Example:

%put *******************************;
%put;
%put Number of observations is &sqlobs..;
%put;
%put *******************************;

Also, the suggestion from @FreelanceReinh is a good idea. The whole idea of writing 17 as a title makes no sense. Titles should contain information — as in text — to describe what the analysis/table/report is.

Re: How to see how many observations were produced by PROC SQL?

Sajid01 — Mon, 06 Feb 2023 13:26:32 GMT

You can show the number of observations it the title as also in color in the log.
The sample code shows how it can be done.
You can use these %put statements either within PROC SQL or after.
I would not prefer to use the %put ERROR. I am showing here just to demonstrate the options.

Title This output has &sqlobs. observations;
Proc sql;
select * from sashelp.fish
where species = 'Pike';;

%put NOTE: The number of observations is &=sqlobs;
%put ERROR: The number of observations is &=sqlobs;
%put WARNING: The number of observations is &=sqlobs;
quit;

The output will be as follows

This is what you would see in the log.

Re: How to see how many observations were produced by PROC SQL?

PaigeMiller — Mon, 06 Feb 2023 13:33:01 GMT

@Sajid01 wrote:

You can show the number of observations it the title as also in color in the log.
The sample code shows how it can be done.
You can use these %put statements either within PROC SQL or after.
I would not prefer to use the %put ERROR. I am showing here just to demonstrate the options.
Title This output has &sqlobs. observations;
Proc sql;
select * from sashelp.fish
where species = 'Pike';;

%put NOTE: The number of observations is &=sqlobs;
%put ERROR: The number of observations is &=sqlobs;
%put WARNING: The number of observations is &=sqlobs;
quit;
The output will be as follows

This is what you would see in the log.

The only reason your title statement works is because you must have run this code twice (or more than twice), and so the second time you run it, &SQLOBS has a value. If you did run it twice, with different SQLs, the &SQLOBS from the first run would appear in the title above the output of the second SQL, and so that's really not a good thing.

If this is the first time you run the code, as in a newly opened SAS session, you get the warning in the log

WARNING: Apparent symbolic reference SQLOBS not resolved.

Re: How to see how many observations were produced by PROC SQL?

Sajid01 — Tue, 07 Feb 2023 00:41:45 GMT

@PaigeMiller
Yes I see I had run it twice.

Re: How to see how many observations were produced by PROC SQL?

Nietzsche — Tue, 07 Feb 2023 02:35:34 GMT

@Ksharp wrote:

proc sql number;
	 select *
	 from sashelp.fish
	 where species="Pike";
quit;

oh wow, that was efficient!