Do you really need to know NOBS? What are you going to do with it?

On Fri, Oct 8, 2010 at 8:11 AM, 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; >