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

[Nietzsche]

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";

[Astounding]

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

[Nietzsche]

---

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

[FreelanceReinh]

---

@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

[Nietzsche]

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?

[FreelanceReinh]

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.

[PaigeMiller]

---

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

[Sajid01]

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.

[PaigeMiller]

---

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

[Sajid01]

@PaigeMiller 
Yes I see I had run it twice. 

[Ksharp · Accepted solution]

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

[Nietzsche]

---

@Ksharp wrote:

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

---

oh wow, that was efficient!