BookmarkSubscribeRSS Feed
MikeCa
Calcite | Level 5
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;
10 REPLIES 10
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Calcite | Level 5
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
Calcite | Level 5
Don't you want...
select Count(*) into :n, COADAcct, THAmt, COADAmt, Variance

and then test for &n.?
SPR
Quartz | Level 8 SPR
Quartz | Level 8
It outputs warning into the LOG. Is it critical to have it in PDF?

SPR
MikeCa
Calcite | Level 5
Yes, I need it in pdf for distribution.

I get the same warning "Into clause specifies fewer host variables .....
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Calcite | Level 5
Perfect! Thanks for another Lesson!
Ksharp
Super User
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
Quartz | Level 8
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1384 views
  • 0 likes
  • 6 in conversation