BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Nietzsche
Lapis Lazuli | Level 10

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

Nietzsche_0-1675666106838.png

 

SAS Base Programming (2022 Dec), Preparing for SAS Advanced Programming (Cancelled).
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
proc sql number;
	 select *
	 from sashelp.fish
	 where species="Pike";
quit;

Ksharp_0-1675686340653.png

 

View solution in original post

11 REPLIES 11
Astounding
PROC Star
Yes, there is an automatically produced macro variable called &sqlobs
Nietzsche
Lapis Lazuli | Level 10

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

Nietzsche_0-1675672502953.png

 

SAS Base Programming (2022 Dec), Preparing for SAS Advanced Programming (Cancelled).
FreelanceReinh
Jade | Level 19

@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
Lapis Lazuli | Level 10

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?

SAS Base Programming (2022 Dec), Preparing for SAS Advanced Programming (Cancelled).
FreelanceReinh
Jade | Level 19

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
Diamond | Level 26

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

--
Paige Miller
Sajid01
Meteorite | Level 14

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 

Sajid01_0-1675689799408.png

This is what you would see in the log.

 

Sajid01_1-1675689879903.png

 

 

PaigeMiller
Diamond | Level 26

@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 

Sajid01_0-1675689799408.png

This is what you would see in the log.

 

Sajid01_1-1675689879903.png

 

 


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.

 

--
Paige Miller
Sajid01
Meteorite | Level 14

@PaigeMiller 
Yes I see I had run it twice. 

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

Ksharp_0-1675686340653.png

 

Nietzsche
Lapis Lazuli | Level 10

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

Ksharp_0-1675686340653.png

 


oh wow, that was efficient!

SAS Base Programming (2022 Dec), Preparing for SAS Advanced Programming (Cancelled).

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1446 views
  • 7 likes
  • 6 in conversation