Automatic variable for counting the no of obs from pass through SQL

[SASAna]

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

[SASKiwi]

What does your SQL look like? It is only populated with a row count if you create a SAS table.

[SASAna]

@SASKiwi

Its Oracle connecting pass through SQL like below.

PROC SQL;
CONNECT TO ORACLE(PATH="&DB." USER=&ID. ORAPW=&Password.);
CREATE TABLE dataset AS SELECT * FROM CONNECTION TO ORACLE
( SELECT
......
);
Disconnect from Oracle;
Quit;

%let COUNT1=&SQLOBS; %put &=COUNT1; ( sqlobs is coming as 0 , but my table 'dataset' has observations.

[Tom · Accepted solution]

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; 

[SASAna]

Thanks Tom. It worked well.

[ChrisNZ]

I thought the data was subsequently subset or joined in the SAS part of the code.Sigh......

[ChrisNZ]

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 ;