LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (October 2010, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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: >* 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;

Back to: Top of message | Previous page | Main SAS-L page