Conditionally printing two reports

[deleted_user]

I am trying to proc print two different reports, based on whether there are any records in a dataset. I have the reports in macros, so I thought it would be easy to call, but I'm very confused as to where and how to do it.

Here's the code:


%macro Error_chk;
/***************************************************************/
/* error check sql here for the case that the where */
/* record_id selected returns no rows */
/***************************************************************/
proc sql;
CREATE VIEW WORK.rec_select
AS SELECT RECORD_ID FROM WORK.revision_gt_1;

%if &sqlobs = 0 %then %do;
%let error = 1;
%end;
Quit;
%mend Error_chk;


%macro Report_selection;
Data _null_;
%if &error = 0 %then %do;
%history_rpt;
%end;
%else %if &error = 1 %then %do;
%err_prt;
%end;
run;
%mend Report_selection;

%Error_chk;
%Report_selection;


********HERE'S THE ERROR MESSAGE I'M GETTING

6 %macro Error_chk;
17 /***************************************************************/
18 /* error check sql here for the case that the where */
19 /* record_id selected returns no rows */
20 /***************************************************************/
21 proc sql;
22 CREATE VIEW WORK.rec_select
23 AS SELECT RECORD_ID FROM WORK.revision_gt_1;
24
25 %if &sqlobs = 0 %then %do;
26 %let error = 1;
27 %end;
28 Quit;
29 %mend Error_chk;
30
31
32 %macro Report_selection;
33 Data _null_;
34 %if &error = 0 %then %do;
35 %history_rpt;
36 %end;
37 %else %if &error = 1 %then %do;
38 %err_prt;
39 %end;
40 run;
41 %mend Report_selection;
42
43 %Error_chk;
NOTE: SQL view WORK.REC_SELECT has been defined.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 27k
Page Faults 0
Page Reclaims 0
Page Swaps 0
Voluntary Context Switches 3
Involuntary Context Switches 0
Block Input Operations 0
2 The SAS System 09:51 Friday, December 19, 2008

Block Output Operations 0


44 %Report_selection;
WARNING: Apparent symbolic reference ERROR not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&error = 0
ERROR: The macro REPORT_SELECTION will stop executing.
45
46 %LET _CLIENTTASKLABEL=;
47 %LET _EGTASKLABEL=;
48 %LET _CLIENTPROJECTNAME=;
49 %LET _SASPROGRAMFILE=;
50
51 ;*';*";*/;quit;run;
____
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 108k
Page Faults 0
Page Reclaims 0
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0


52 ODS _ALL_ CLOSE;
NOTE: ODS PDF(EGPDF) printed no output.
(This sometimes results from failing to place a RUN statement before the ODS PDF(EGPDF) CLOSE statement.)
53
54
55 QUIT; RUN;
56


THANKS,

[Cynthia_sas]

Hi:
You should read about the difference between GLOBAL and LOCAL macro variables. If you create &ERROR in a macro program, generally &ERROR would be "local" in scope and therefore, would NOT be available AFTER %Error_chk had finished processing.

Also, you do NOT show what's inside %history_rpt or %err_prt, however, unless they are data step programs, you do not need to invoke them INSIDE a DATA _NULL_ step (which seems to be happening in your %Report_selection macro program). A %IF test can be done inside a macro program without being in a datasetp program. In fact , if %history_rpt or %err_prt use ODS or SAS procedures, you're probably better off just getting rid of the DATA _NULL_ that you have in the second macro definition.

Finally, I see where you set &ERROR to 1, but I don't see where you set or initialize it to 0. Macro variables are initially set to nothing or NULL, not zero. You might put --
[pre]
%global error;
%let error = 0;
[/pre]

before the proc sql step.

That may not solve all your issues, however. You probably have a logic problem, as well. The first rule of writing a macro program is beginning with a working SAS program. Your underlying assumption seems to be that you have an error if SQLOBS is 0. However, when I run the same query -- once to create a table and a second time to create a view, I get sqlobs=0 for the view. According to the documentation, SQLOBS always is 0 if a view is created (refer to to the documentation here:
http://support.sas.com/documentation/cdl/en/sqlproc/59727/HTML/default/a001360983.htm#a001404680) And, you can prove it to yourself, by running these 2 SQL steps:
[pre]
1264 proc sql;
1265 create table work.reg1 as
1266 select * from sashelp.shoes
1267 where region = 'Asia';
NOTE: Table WORK.REG1 created, with 14 rows and 7 columns.

1268 %put **** 1) create table sqlobs = &sqlobs;
**** 1) create table sqlobs = 14
1269 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


1270
1271 proc sql;
1272 create view WORK.reg2
1273 as SELECT * FROM sashelp.shoes
1274 where region = 'Asia';
NOTE: SQL view WORK.REG2 has been defined.
1275 %put **** 2) create view sqlobs = &sqlobs;
**** 2) create view sqlobs = 0
1276 Quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

[/pre]


This means that even if &ERROR were available, it would always be set to 1, because as long as you are basing SQLOBS on view creation, it will always be 0.

A different technique for testing whether there are any records in a dataset is to capture NOBS from dictionary.tables:
[pre]
proc sql;
select (nobs - delobs) into :numobs
from dictionary.tables
where libname = "WORK" and memname = "REVISION_GT_1";
quit;
%let numobs = &numobs;
%put *** number of obs in dataset is: &numobs;

[/pre]

Then you could test &NUMOBS:
%if &NUMOBS = 0 %then %do; %let error = 1; %end;

OR, you could just use &NUMOBS instead of &ERROR in your macro program.

cynthia