Date:         Fri, 8 Oct 2010 12:27:24 -0400
Reply-To:     Suzanne McCoy <Suzanne.McCoy@CATALINAMARKETING.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Suzanne McCoy <Suzanne.McCoy@CATALINAMARKETING.COM>
Subject:      Re: nobs macro review request
Comments: To: "Data _null_;" <iebupdte@gmail.com>
In-Reply-To:  <AANLkTik=tVkJcyZDYqqow2Y6d9Tn9P4H0C7JTBaAZPQ-@mail.gmail.com>
Content-Type: text/plain; charset="us-ascii"

Here's the final version of the macro.  99% of the time it is going to be counting rows in a table in one of the 3 main types of databases so that was the main objective for this exercise.  We have an entire team of DBA type folks who are starting to do ETL development in SAS so we'll call this the 'DBA friendly' version.  Thanks everyone for the input.  That's the quickest and most thorough code review I've participated in for years.  I've incorporated as much of the feedback as I could.

/*-------------------------------------------------------------------
* Program: nobs.sas
* Purpose: return the number of rows in a dataset or database table
* Author:  Suzanne McCoy 7Oct2007
* Usage Note: By default the macro variable generated is &NOBS. Use
*             parameter macro_var_name to override the name if needed.
--------------------------------------------------------------------*/

%macro nobs(lib=
           ,dsn=
           ,macro_var_name=NOBS
           );

 * if the macro variable exists then the value will be replaced;
 * if not then declare it globally so values can be used throughout the SAS session;
 %if not %symexist(&macro_var_name) %then %global &MACRO_VAR_NAME;

 %let &MACRO_VAR_NAME=0;  *initialize to zero;

 %if NOT %sysfunc(exist(&&LIB..&DSN)) %then
 %do;
   %put ----- Execution note: &&LIB..&DSN cannot be found -----;
   %goto exit;
 %end;

 *Dictionary tables cannot return the number of rows in a database table;
 *but can return the number of observations in a SAS dataset.;
 *Passing in a database table name will put a note in the log saying;
 *that no rows were returned from the query.;
 proc sql noprint noerrorstop;
   select nlobs into :&MACRO_VAR_NAME.
   from   dictionary.tables
   where  libname=upcase("&LIB")
     and  memname=upcase("&DSN")
   ;
 quit;

 *This section will count the rows in a database table that physically exists;
 %if %sysfunc(exist(&&LIB..&DSN)) and &SQLOBS = 0 %then
 %do;
    proc sql noprint noerrorstop;
     select count(*) into :&MACRO_VAR_NAME.
     from &&LIB..&DSN
     ;
    quit;
 %end;

 %exit:

%mend nobs;

-----Original Message-----
From: Data _null_; [mailto:iebupdte@gmail.com]
Sent: Friday, October 08, 2010 11:41 AM
To: Suzanne McCoy
Cc: SAS-L@listserv.uga.edu
Subject: Re: nobs macro review request

Do you really need to know NOBS?  What are you going to do with it?  [Fix the code to use nlobs instead ;) Suzanne]

snip