topic Automatic variable for counting the no of obs from pass through SQL in SAS Programming

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

SASAna — Wed, 27 May 2020 22:45:03 GMT

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

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

SASKiwi — Thu, 28 May 2020 01:58:03 GMT

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

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

ChrisNZ — Thu, 28 May 2020 05:13:15 GMT

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 ;

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

SASAna — Thu, 28 May 2020 15:25:38 GMT

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

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

Tom — Thu, 28 May 2020 15:39:41 GMT

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;

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

SASAna — Thu, 28 May 2020 16:09:25 GMT

Thanks Tom. It worked well.

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

ChrisNZ — Thu, 28 May 2020 21:27:26 GMT

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