topic Re: Proc SQL Query -- Test for No Observations and Output Message in SAS Procedures

Proc SQL Query -- Test for No Observations and Output Message

MikeCa — Mon, 21 Mar 2011 16:27:36 GMT

I'm using the following SQL procedure to query any variances that exist. When no variances exist I would like to print out the message "No Variances were Found!" How do I test for that in the SQL procedure? Thanks

ods listing close;
options nonumber nodate;
ods escapechar = "^";
ods pdf file = "U:\Reconcilement_COAD_TH_Files.pdf";

Title1 "^S={just=C color=blue}Reconcile COAD File to TH File -- Below are the Variances";
proc sql;
select COADAcct, THAmt, COADAmt, Variance
from Combined c
where c.Variance ^=0;
quit;
ods pdf close;
ods listing;

Re: Proc SQL Query -- Test for No Observations and Output Message

SPR — Mon, 21 Mar 2011 17:41:31 GMT

Hello MikeCa,

This is possible solution:
[pre]
%macro a;
ods listing close;
options nonumber nodate;
ods escapechar = "^";
/*ods pdf file = "U:\Reconcilement_COAD_TH_Files.pdf";*/

Title1 "^S={just=C color=blue}Reconcile COAD File to TH File -- Below are the Variances";
proc sql;
select Count(*) as N, COADAcct, THAmt, COADAmt, Variance into :n
/*from Combined c*/
from c
where c.Variance ^=0;
quit;
%if &n=0 %then %put No Variances were Found!;
ods pdf close;
ods listing;
%mend a;
%a
[/pre]
Sincerely,
SPR

Re: Proc SQL Query -- Test for No Observations and Output Message

MikeCa — Tue, 22 Mar 2011 13:43:39 GMT

Thanks, I've run it and understand it somewhat. The piece I don't get is that it is printing a null record in the pdf output instead of the Phrase "No Variances were Found!? Any Hints?

Re: Proc SQL Query -- Test for No Observations and Output Message

DavidJ — Tue, 22 Mar 2011 13:53:20 GMT

Don't you want...
select Count(*) into :n, COADAcct, THAmt, COADAmt, Variance

and then test for &n.?

Re: Proc SQL Query -- Test for No Observations and Output Message

SPR — Tue, 22 Mar 2011 14:10:22 GMT

It outputs warning into the LOG. Is it critical to have it in PDF?

SPR

Re: Proc SQL Query -- Test for No Observations and Output Message

MikeCa — Tue, 22 Mar 2011 14:16:49 GMT

Yes, I need it in pdf for distribution.

I get the same warning "Into clause specifies fewer host variables .....

Re: Proc SQL Query -- Test for No Observations and Output Message

SPR — Tue, 22 Mar 2011 14:44:27 GMT

How about this variant:
[pre]
data c;
COADAcct=1; THAmt=1; COADAmt=3; Variance=0; output;
COADAcct=2; THAmt=10; COADAmt=8; Variance=0; output;
run;
%macro a;
ods listing close;
options nonumber nodate;
ods escapechar = "^";
/*ods pdf file = "U:\Reconcilement_COAD_TH_Files.pdf";*/
proc SQL noprint;
select Count(*) as N into :n
from c
where c.Variance ^=0;
quit;
%if &n=0 %then %do;
Title1 "^S={just=C color=blue}No Variances were Found!";
proc SQL;
select Variance
from c
where Variance ^=0;
quit;
%end;
%else %do;
Title1 "^S={just=C color=blue}Reconcile COAD File to TH File -- Below are the Variances";
proc sql;
select COADAcct, THAmt, COADAmt, Variance
/*from Combined c*/
from c
where c.Variance ^=0;
quit;
%end;
ods pdf close;
ods listing;
%mend a;

%a
[/pre]
SPR

Re: Proc SQL Query -- Test for No Observations and Output Message

MikeCa — Tue, 22 Mar 2011 15:38:24 GMT

Perfect! Thanks for another Lesson!

Re: Proc SQL Query -- Test for No Observations and Output Message

Ksharp — Wed, 23 Mar 2011 02:17:33 GMT

SPR is wrong. Title statement is global statement ,You can not use it conditionally.

[pre]

[/pre]

%macro title;

data temp;

title='No Variances were Found!';

run;

ods listing close;

options nonumber nodate;

ods escapechar = "^";

ods pdf file = "c:\x.pdf";

Title1 "^S={just=C
color=blue}Reconcile COAD File to TH File -- Below are the Variances";

proc sql;

select region,product

from sashelp.shoes c

where
c.region eq 'China';

%if &sqlobs. eq 0 %then %do;

select title
label='#'

from
temp;

%end;

quit;

ods pdf close;

ods listing;

%mend;

%title

Ksharp

Re: Proc SQL Query -- Test for No Observations and Output Message

polingjw — Wed, 23 Mar 2011 10:02:48 GMT

Actually SPR’s program works fine. While a title statement is a global statement, the statement is never passed to the input stack to be executed unless the %if statement is true. You can’t conditionally execute global statements in a data step but you can conditionally execute them in a macro.

Re: Proc SQL Query -- Test for No Observations and Output Message

sbb — Wed, 23 Mar 2011 12:38:37 GMT

Honestly, Ksharp you need to sharpen your mouse-cursor, given recent inaccurate post-replies on the forums here.

As mentioned by polingjw, a TITLE statement can be conditionally executed in various ways, one showed was with a MACRO invocation, using PROC SQL to detect a condition, setting a macro variable indicator, and testing that indicator to determine code-path.

Also, consider the TOP "clause" equivalent, your SAS code post-reply provided no "group-level" TOP(nn) equivalent, only where your code generates observations that have the MAX(SALES) value, instead.

Scott Barry
SBBWorks, Inc. Message was edited by: sbb