Help using Base SAS procedures

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

Reply
Occasional Contributor
Posts: 16

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

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;
Super Contributor
Super Contributor
Posts: 365

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

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
Occasional Contributor
Posts: 16

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

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?
Contributor
Posts: 32

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

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

and then test for &n.?
Super Contributor
Super Contributor
Posts: 365

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

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

SPR
Occasional Contributor
Posts: 16

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

Yes, I need it in pdf for distribution.

I get the same warning "Into clause specifies fewer host variables .....
Super Contributor
Super Contributor
Posts: 365

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

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
Occasional Contributor
Posts: 16

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

Perfect! Thanks for another Lesson!
Super User
Posts: 10,035

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

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



Regular Contributor
Posts: 171

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

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.
Super Contributor
Super Contributor
Posts: 3,174

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

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
Ask a Question
Discussion stats
  • 10 replies
  • 221 views
  • 0 likes
  • 6 in conversation