Date:         Fri, 8 Oct 2010 09:11:45 -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:      nobs macro review request

Am I missing any conditions?  I know there are other ways to do this but
the preferred method here is SQL because all of the programmers understand
it and we have a huge database footprint with Oracle, Red Brick and the
mack daddy Netezza.  The OS is AIXR 64 bit with SAS 9.2 (TS2M3) .  This is
working fine but if I am missing any conditions I want to account for them
before I roll this into production.

Thanks in advance for your valuable input,
Suzanne

/*-------------------------------------------------------------------
* 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
           );

 %global &MACRO_VAR_NAME; *so it exists throughout the SAS session;
 %let &MACRO_VAR_NAME=0;  *initialize to zero;

 *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 nobs 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;

 *if the table or dataset does not exist then a message is placed in the
log;
 %else  %if NOT %sysfunc(exist(&&LIB..&DSN)) %then
   %put ----- Execution note: &&LIB..&DSN cannot be found -----;

%mend nobs;