BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metallon
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
jakarman
Barite | Level 11

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

3 REPLIES 3
jakarman
Barite | Level 11

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 --<-----
metallon
Pyrite | Level 9

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;

jakarman
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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