Date:         Fri, 8 Oct 2010 09:38:58 -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

Blew it when I added the last comment line and didn't test it one more
time.  Either delete that comment or change it to /* */ syntax.  Thanks
Andre!


On Fri, 8 Oct 2010 09:11:45 -0400, Suzanne McCoy
<suzanne.mccoy@CATALINAMARKETING.COM> wrote:

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