topic Re: row count with nobs in SAS Procedures

row count with nobs

metallon — Wed, 01 Apr 2015 08:39:02 GMT

Hi SAS Experts,

I spent a few hours with google and found nothing shorter than the following to get the row count:

%let dsid=%sysfunc(open(mydataSet));

%let num=%sysfunc(attrn(&dsid,nlobs));

%let rc=%sysfunc(close(&dsid));

%put There are &num observations in dataset one;

Is there a quicker way? the variable &num appears later in a proc report title.

Re: row count with nobs

jakarman — Wed, 01 Apr 2015 08:50:04 GMT

This only works with SAS-dataset not with external DBMS-tables. With SAS datasets it is the fastest and direct approach.

It depends on your logical needs if this is the most handy approach.
1/ The open attrn close are SAS functions. You can use them in a SAS-datastep

This is the quickest one when working with a dataset that should contain those retrieved information. (avoid overhead macro interaction)

2/ Having working on a dataset you have firs/last processing at that moment knowing those numbers and may be stored.

3/ SAS(R) 9.4 Statements: Reference, Third Edition Set is having curobs and nobs as options. Very valuable when processing the data.

Take care as the number is having the count included of records marked as being deleted.(modify result)

Re: row count with nobs

metallon — Wed, 01 Apr 2015 09:40:33 GMT

Hi Jaap,

thanks for the answer.

The data does come from proc sql but I create a SAS data set for further interaction like client side sorting, filtering, styling.

proc sql;

connect to oracle as nrkpdb (user=xxx password=yyyy path=Dxxx);

create table positivlist as

(

select *

from connection to nrkpdb

(

SELECT* from dual

)

);

disconnect from nrkpdb;

quit;

Re: row count with nobs

jakarman — Wed, 01 Apr 2015 12:32:30 GMT

metallon,

For an external DBMS the Sql count(*) is a common exception retrieving information.

COUNT (Transact-SQL) (MS SQL)

COUNT (Oracle SQL)
What I can and allowed to do is the DBMS optimization possible an active dictionary on those side. Nothing beats that one.

With bigger data (above several GB's these days) is often more profitable to use data federation techniques or in database processing. ( prod ds2 , FED SQL).
Leaving the data as long as possible at the DBMS side doing the work there. Only at the moment of reporting/analyses getting it to SAS.
Filtering and sorting are tasks that can be done by a well performing DBMS better. When the DBMS design is a bad one and there is no way top get that optimized you are better with a copied version with SAS.

It is all up to you to analyze the situation and decide accordingly.