Hi SAS users,
Are there any automatic variables like SQLOBS for counting the number of observations from pass through SQL ?
SQLOBS worked for me for SAS Proc SQL's , but not Pass through.
In my code I have count the extraction for each iterations and load those counts into Oracle table for statistics.
Thanks,
Ana
So you aren't trying to count activity done inside of the remote database. You are just waiting too long to capture the value of SQLOBS. You have to get it before SQL runs another statement.
PROC SQL;
CONNECT TO ORACLE(PATH="&DB." USER=&ID. ORAPW=&Password.);
CREATE TABLE dataset AS SELECT * FROM CONNECTION TO ORACLE
( SELECT
......
);
%let COUNT1=&SQLOBS;
disconnect from Oracle;
quit;
%put &=COUNT1;
What does your SQL look like? It is only populated with a row count if you create a SAS table.
So you aren't trying to count activity done inside of the remote database. You are just waiting too long to capture the value of SQLOBS. You have to get it before SQL runs another statement.
PROC SQL;
CONNECT TO ORACLE(PATH="&DB." USER=&ID. ORAPW=&Password.);
CREATE TABLE dataset AS SELECT * FROM CONNECTION TO ORACLE
( SELECT
......
);
%let COUNT1=&SQLOBS;
disconnect from Oracle;
quit;
%put &=COUNT1;
I think you have to derive this count yourself.
For example with Oracle you can do something like:
proc sql noprint;
connect using ORALIB;
create table T as
select * from connection to ORALIB (
with Q as ( .. your oracle query... )
select *
from Q
, (select count(*) as COUNT from Q)
) ...more SAS code, inner join, where etc.... ;
select COUNT into :sqloraobs from T(obs=1);
%put &=sqloraobs ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: