Date: Mon, 21 May 2007 23:55:23 +0000
Reply-To: iw1junk@COMCAST.NET
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ian Whitlock <iw1junk@COMCAST.NET>
Subject: Re: SQL ODS prevent title when select grabs no row
Summary: Clean up titles, SQL statements execute at the semi-colon
#iw-value=1
Richard,
I think the simplest solution is clean up afterward. For example,
title "Richard's problem" ;
title2 "QQQ" ;
data w ; x = 1 ; run ;
proc sql;
/* preparation */
reset noprint ;
select put(number, 2. -l), text into :tl, :text
from dictionary.titles
where type = "T"
having number = max(number)
;
reset print ;
/* end preparation */
title2 "Data entry errors may have occurred";
select * from
sashelp.class as A, sashelp.class as B
where A.name ne B.name
and abs(a.age-b.age) < 2
and abs(a.weight-b.weight) > 80
;
/* clean up */
title&tl "&text" ;
/* test */
select * from w ;
quit;
There are problems when you get fancy since DICTIONARY.TITLES fails to
capture any more than the crudest level of information. The only answer to
that is write a macro to handle all titles and footnotes and generate your
own "dictionary" information. Perhaps also send suggestions to SI to get
realistic and save the complete title or footnote statement.
The alternative is to use Toby's idea - don't make the title in the first
place. First capture NOBS then decide whether to make the title.
Advantage - avoids problems mentioned above. Disadvantage - may be costly
to execute to get NOBS. Of course,
reset noprint ;
select put(count(*),8. -l) into: NOBS
from sashelp.class as A, sashelp.class as B
where A.name ne B.name
and abs(a.age-b.age) < 2
and abs(a.weight-b.weight) > 80
;
reset print ;
shouldn't be that expensive when one is expecting 0 observations.
Ian Whitlock
==============
Date: Mon, 21 May 2007 17:54:35 -0400
Reply-To: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender: "SAS(r) Discussion"
From: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject: Re: SQL ODS prevent title when select grabs no row
Content-Type: text/plain; charset=ISO-8859-1
On Mon, 21 May 2007 18:08:46 +0000, toby dunn <tobydunn@HOTMAIL.COM> wrote:
>Richard ,
>
>Hmmmm well one could play off of how SQL executes code and Macro
Execution
>time:
>
>%Macro Title( NObs = ) ;
> %If ( &NObs > 0 ) %Then %Do ;
> title "Data entry errors may have occurred";
> %End ;
>%Mend Title ;
>
>proc sql;
> select * from
> sashelp.class as A, sashelp.class as B
> where A.name ne B.name
> and abs(a.age-b.age) < 2
> and abs(a.weight-b.weight) > 80
> ;
>%Title( Nobs = &SqlObs )
>quit;
But the SELECT statement runs and generates its output before the macro
runs.
>
>
>
>Toby Dunn
>
>On the other hand, you have different fingers. ~ LCG
>
>The early bird may get the worm, but the second mouse gets the cheese in
the
>trap. ~ LCG
>
>What happens if you get scared half to death, twice? ~ LCG
>
>
>
>
>
>From: "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
>Reply-To: "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
>To: SAS-L
>Subject: SQL ODS prevent title when select grabs no row
>Date: Mon, 21 May 2007 13:57:07 -0400
>
>A SQL select statement (join) is used to report anomolous data. Sometimes
>the select results in no rows.
>
>Is there an easy way to keep SQL from outputting a title when there are no
>rows ?
>
>Example:
>
>title "Data entry errors may have occurred";
>proc sql;
> select * from
> sashelp.class as A, sashelp.class as B
> where A.name ne B.name
> and abs(a.age-b.age) < 2
> and abs(a.weight-b.weight) > 80
> ;
>quit;
>
>--
>Richard A. DeVenezia
>
>_________________________________________________________________
>More photos, more messages, more storage?get 2GB with Windows Live
Hotmail.
>
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=
TXT_TAGHM_migration_HM_mini_2G_0507
