Date:         Sat, 6 Aug 2005 08:01:53 -0400
Reply-To:     "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Subject:      Re: Detecting when a where clause results in no observations
Comments: To: sas-l@uga.edu

data _null_; wrote:
> If you make your WHERE a subsetting IF you can create a data set of
> the observations of interest while also making an OBS variable to
> satisfy the observation requirement.  You only pass the larger data
> set once. Consider...
>
> %macro makrep(data=,where=age eq 30);
>    %local hasobs;
>    %let hasobs = 0;
>    data work.report;
>       retain _flag_ 1;
>       set &data;
>       if &where; /* make the WHERE subsetting IF */

This seems plain silly.  Why change a WHERE to an IF?  Remember, WHERE has
some operators not available to IF (such as LIKE)

One is better served with the ATTRN function, using attr-name of ANY, NOBS
or NLOBSF {"Tip: Passing NLOBSF to ATTRN requires the engine to read every
observation from the data set that matches the WHERE clause. Based on the
file type and size, this can be a time-consuming process
"}


Examples:
---------------
data _null_;
  ds = open ('sashelp.class(where=(name="Joe")');
  any  = attrn(ds,'ANY');
  nobs = attrn(ds,'NOBS');
  nlobsf = attrn(ds,'NLOBSF');
  put (any n:)(=);
  ds = close (ds);


  ds = open ('sashelp.class(where=(name like "J%")');
  any  = attrn(ds,'ANY');
  nobs = attrn(ds,'NOBS');
  nlobsf = attrn(ds,'NLOBSF');
  put (any n:)(=);
  ds = close (ds);
run;
---------------


The function calls can be escaped from the confines of a DATA Step (and thus
a step boundary) by using the macro environment:
---------------
%let ds = %sysfunc (OPEN(sashelp.class(where=(name like "J%"))));
%put %sysfunc(ATTRN(&ds,ANY));
%let ds = %sysfunc(CLOSE(&ds));
---------------

--
Richard A. DeVenezia
http://www.devenezia.com/