Date:         Sun, 7 Aug 2005 00:59:32 +1000
Reply-To:     Scott Bass <usenet739_yahoo_com_au@ALFREDO.CC.UGA.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Scott Bass <usenet739_yahoo_com_au@ALFREDO.CC.UGA.EDU>
Subject:      Re: Detecting when a where clause results in no observations
Comments: To: sas-l@uga.edu

"Scott Bass" <usenet739_yahoo_com_au> wrote in message
news:42f2e6e1$0$16468$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
> OK, I know this is simple, but I wanted to get your opinion whether this
> is the best approach...
>
> I'm writing a program that will print a series of diagnostic messages
> against input datasets.  The tests will be a printout of those
> observations matching a where clause.  I'd like output for all the tests,
> even those without an error.  I'd like to also print the observation
> number of the input dataset, as this will assist in debugging of the input
> data.
>
> If it weren't for the obs num requirement, I'd use SQL:
>
> data test;
>   do x=1 to 10;
>      output;
>   end;
> run;
>
> %macro test;
> proc sql;
>   title "Test 1";
>   select * from test where x in (2,4,6,8,10); /* error condition */
>   %if not &sqlobs %then
>   select "No errors detected" from test (obs=1);
>   ;
>   title "Test 2";
>   select * from test where x = 99;
>   %if not &sqlobs %then
>   select "No errors detected" from test (obs=1);
>   ;
> quit;
> %mend;
> %test;
>
> However, since the PROC's don't have the equivalent of &sqlobs (right???),
> the only approach I can think of is two passes over the data (yuck) with
> the same where clause:
>
> %macro test2;
> proc sql;
>   select count(*) into :nobs from test where x in (2,4,6,8,10);
> quit;
> title "Test1";
> proc print data=one;
>   where x in (2,4,6,8,10);
> run;
> %if not &nobs %then %do;
> data _null_;
>   file print;
>   put "No errors found";
> run;
>
> proc sql;
>   select count(*) into :nobs from test where x = 99;
> quit;
> title "Test2";
> proc print data=one;
>   where x = 99;
> run;
> %if not &nobs %then %do;
> data _null_;
>   file print;
>   put "No errors found";
> run;
> %mend;
> %test2;
>
> Questions:
>
> 1.  Is there an equivalent of &sqlobs for a PROC invocation?  I did a %put
> _automatic_ but didn't see anything.
> 2.  Is there a way to get SQL to print the obs num from the input dataset?
> I think not, that's not how SQL works, either conceptually or technically.
> 3.  Any other ideas, or is this approach as good as any?
>
> Thanks,
> Scott

Sincere thanks to all for their replies.  This newsgroup is far and away the
most helpful of the ones I frequent.

I've given all the solutions posted a try, and have decided on the following
(a variation of Dennis' posting):

data one;do x=1 to 10;output;end;run;
title3 "This is my test";
%macro print(data=,where=);
   data _null_;
      if eof then do;
         file print;
         put / "No errors found";
      end;
      stop;
      set &data end=eof;
      where &where;
   run;
   proc print data=&data;
      where &where;
   run;
%mend;
%print(data=one,where=x>5);
%print(data=one,where=x>99);
%print(data=one,where=x in (2,4,6,8,10));  * test parsing of () in where
clause ;

This is MUCH more efficient than my original approach.  It only reads 0 | 1
observation from the input dataset.  I also decided NOT to create a macro
variable in the data step and conditionally execute the proc print.  I tried
both approaches, and think the diagnostic NOTE messages in the log are
better this way.  I like the consistent pairing of data _null_ and proc
print note messages, with the number of records read for both displayed in
the log.

Note that I didn't want to actually subset the input data and then print
that, since I want the *original* observation numbers of the input data
printed in the output.  This makes debugging the raw data easier.

I also liked the SCL approach, but figured I can't get much more efficient
than reading a single record, which is all I need to do for my particular
purpose.

Dennis, special thanks to you for suggesting such a simple yet elegant
approach.  I'm so used to putting the set statement near the top of the data
step, rather than at the bottom.

Again, thanks so much for all your help regarding better approaches for my
scenario.  Much appreciated.

Regards,
Scott