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

[MikeCa]

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;

[SPR]

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

[MikeCa]

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?

[DavidJ]

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

and then test for &n.?

[SPR]

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

SPR

[MikeCa]

Yes, I need it in pdf for distribution.

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

[SPR]

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

[MikeCa]

Perfect! Thanks for another Lesson!

[Ksharp]

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

[polingjw]

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.

[sbb]

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