Help using Base SAS procedures

row count with nobs

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

row count with nobs

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.


Accepted Solutions
Solution
‎04-01-2015 04:50 AM
Trusted Advisor
Posts: 3,215

Re: row count with nobs

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)  
 

---->-- ja karman --<-----

View solution in original post


All Replies
Solution
‎04-01-2015 04:50 AM
Trusted Advisor
Posts: 3,215

Re: row count with nobs

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)  
 

---->-- ja karman --<-----
Frequent Contributor
Posts: 133

Re: row count with nobs

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;

Trusted Advisor
Posts: 3,215

Re: row count with nobs

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.

---->-- ja karman --<-----
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 700 views
  • 3 likes
  • 2 in conversation