How to stop Excel Sheets from popping up in SAS Results Viewer window

chandler — Mon, 04 Oct 2010 16:41:56 GMT

Using SAS 9.1.3 with SP4, on Windows XP Pro Platform,with SP3. I use PROC REPORT and ODS to output HTML files for Excel 2003 to read as spreadsheets for my reports. I didn't knowingly modify my SAS code, but for some reason, each of the 20 spreadsheets are popping up in the Results Viewer display window, one at a time as they are created. How do I stop this behaviour ?

Presently, I'm having to go in and manually close all those spreadsheets, before the program will move on to other steps.

Here is my code:

ods listing close ;

%macro loanrpt(uwgrp=,uwsubset=) ;

data &uwgrp._loans_&monyr ;
set all_loans_&monyr ;
where channel in (&uwsubset) ;
run;

proc summary data=all_loans_&monyr nway ;
where measure like 'DBus%' and channel in (&uwsubset) ;
class new_renew c_a ;
var meas_amt ;
output out=&uwgrp._totals (drop=_type_ _freq_) sum= ;
run;

proc delete data=totfmt ;
run;

data totfmt ;
set &uwgrp._totals ;
length start $4. n_r $1.;
n_r = substr(new_renew,1,1) ;
if c_a = 'Count' then
start = n_r||'CNT' ;
else
if c_a = 'Balance' then
start = n_r||'BAL' ;
else
start = n_r||'LIM' ;
label = left(put(meas_amt,12.)) ;
fmtname = "$RTOTAL" ;
run;

proc sort data=totfmt nodupkey ;
by start ;
run;

proc format cntlin=totfmt;
run;

TITLE j=l "" ;

title2 ' ' ;
title3 "AnybankUSA Business Banking" ;

ods html body="\\ccsas1\blc_whse\dhub\Reports_monthly\output\loan_exc_&uwgrp._&monyr._new.xls" ;

title4 "Loan System Exceptions - &uwgrp " ;
title5 "New - &monyr " ;

PROC REPORT DATA=&uwgrp._loans_&monyr LS=159 PS=67 SPLIT="/"
HEADLINE HEADSKIP CENTER MISSING nowd ;
where new_renew = 'NEW' ;

COLUMN ( measure c_a group,( meas_amt ) G_Total new_renew perc_tot);

DEFINE measure / GROUP FORMAT= $75. WIDTH=25 SPACING=2 FLOW LEFT "Measure" ;
DEFINE c_a / GROUP FORMAT= $16. WIDTH=16 SPACING=2 LEFT "Data" ;
DEFINE group / ACROSS FORMAT= $SPCHDR23. WIDTH=23 SPACING=2 LEFT ORDER=formatted " " ;
DEFINE meas_amt / SUM FORMAT= COMMA9. WIDTH=9 SPACING=2 RIGHT " " ;
DEFINE G_Total / COMPUTED FORMAT= COMMA10. WIDTH=10 SPACING=2 RIGHT "Grand/Total/" ;
define new_renew / group noprint ;
DEFINE perc_tot / COMPUTED FORMAT= percent7.2 WIDTH=8 SPACING=2 RIGHT "Pct/Total" ;

compute perc_tot ;
if g_total ne 0 then
do;
if new_renew = 'NEW' then
if c_a = 'Count' then
lookup = "NCNT" ;
else
if c_a = 'Balance' then
lookup = "NBAL" ;
else
lookup = "NLIM" ;
else
if c_a = 'Count' then
lookup = "RCNT" ;
else
if c_a = 'Balance' then
lookup = "RBAL" ;
else
lookup = "RLIM" ;

overall_tot = input(put(lookup,$RTOTAL.),12.) ;
perc_tot = g_total / overall_tot ;
end;
else
perc_tot = 0 ;
endcomp;

COMPUTE measure / CHAR LENGTH=75 ;
measure = substr(measure, 2, 74);
ENDCOMP;

COMPUTE G_Total;
g_total = 0;
if _c3_ ge 0 then g_total = _c3_ +g_total;
if _c4_ ge 0 then g_total = _c4_ +g_total;
if _c5_ ge 0 then g_total = _c5_ +g_total;
if _c6_ ge 0 then g_total = _c6_ +g_total;
if _c7_ ge 0 then g_total = _c7_ +g_total;
if _c8_ ge 0 then g_total = _c8_ +g_total;
if _c9_ ge 0 then g_total = _c9_ +g_total;
if _c10_ ge 0 then g_total = _c10_ +g_total;
if _c11_ ge 0 then g_total = _c11_ +g_total;
if _c12_ ge 0 then g_total = _c12_ +g_total;
ENDCOMP;
run;

ods html close ;

ods html body="\\ccsas1\blc_whse\dhub\Reports_monthly\output\loan_exc_&uwgrp._&monyr._renew.xls" ;

title5 "Renew - &monyr " ;

PROC REPORT DATA=&uwgrp._loans_&monyr LS=159 PS=67 SPLIT="/"
HEADLINE HEADSKIP CENTER MISSING nowd ;
where new_renew = 'REN' ;

COLUMN ( measure c_a group,( meas_amt ) G_Total new_renew perc_tot);

DEFINE measure / GROUP FORMAT= $75. WIDTH=25 SPACING=2 FLOW LEFT "Measure" ;
DEFINE c_a / GROUP FORMAT= $16. WIDTH=16 SPACING=2 LEFT "Data" ;
DEFINE group / ACROSS FORMAT= $spchdr23. WIDTH=23 SPACING=2 LEFT ORDER=formatted " " ;
DEFINE meas_amt / SUM FORMAT= COMMA9. WIDTH=9 SPACING=2 RIGHT " " ;
DEFINE G_Total / COMPUTED FORMAT= COMMA10. WIDTH=10 SPACING=2 RIGHT "Grand/Total/" ;
define new_renew / group noprint ;
DEFINE perc_tot / COMPUTED FORMAT= percent7.2 WIDTH=8 SPACING=2 RIGHT "Pct/Total" ;

COMPUTE measure / CHAR LENGTH=75 ;
measure = substr(measure, 2, 74);
ENDCOMP;

COMPUTE G_Total;
g_total = 0;
if _c3_ ge 0 then g_total = _c3_ +g_total;
if _c4_ ge 0 then g_total = _c4_ +g_total;
if _c5_ ge 0 then g_total = _c5_ +g_total;
if _c6_ ge 0 then g_total = _c6_ +g_total;
if _c7_ ge 0 then g_total = _c7_ +g_total;
if _c8_ ge 0 then g_total = _c8_ +g_total;
if _c9_ ge 0 then g_total = _c9_ +g_total;
if _c10_ ge 0 then g_total = _c10_ +g_total;
if _c11_ ge 0 then g_total = _c11_ +g_total;
if _c12_ ge 0 then g_total = _c12_ +g_total;
ENDCOMP;

compute perc_tot ;
if g_total ne 0 then
do;
if new_renew = 'NEW' then
if c_a = 'Count' then
lookup = "NCNT" ;
else
if c_a = 'Balance' then
lookup = "NBAL" ;
else
lookup = "NLIM" ;
else
if c_a = 'Count' then
lookup = "RCNT" ;
else
if c_a = 'Balance' then
lookup = "RBAL" ;
else
lookup = "RLIM" ;

overall_tot = input(put(lookup,$RTOTAL.),12.) ;
perc_tot = g_total / overall_tot ;
end;
else
perc_tot = 0 ;
endcomp;

run;

ods html close ;

%mend ;

%loanrpt(uwgrp = EXPRESS,uwsubset = "EXPRESS") ;

%loanrpt(uwgrp = STANDARD,uwsubset = "STANDARD") ;

%loanrpt(uwgrp = CUSTOM, uwsubset = "CUSTOM") ;

%loanrpt(uwgrp = ALL, uwsubset = "EXPRESS" "STANDARD" "CUSTOM") ;

Re: How to stop Excel Sheets from popping up in SAS Results Viewer window

Cynthia_sas — Mon, 04 Oct 2010 18:38:35 GMT

Hi:
You might check the setting:
Tools ► Options ► Preferences ►
Results ► View results as they are generated

and if "View results as they are generated" is checked or turned on -- uncheck it or turn it off. That means you will have to explicitly click on the output icon to launch Excel in order to view the results.

cynthia

Re: How to stop Excel Sheets from popping up in SAS Results Viewer window

Cynthia_sas — Mon, 04 Oct 2010 18:39:48 GMT

Also useful:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

... which explains how to use the [pre] and [/pre] tags around your code and output in order to maintain indenting and spacing.

Re: How to stop Excel Sheets from popping up in SAS Results Viewer window

chandler — Wed, 06 Oct 2010 17:17:50 GMT

Thank you, Cynthia@SAS. It certainly was set to "View results as they are generated". I unchecked that box and it solved the problem. Is that some default setting that might have been reset when we renewed our SAS license back in June of this year ? It just started happening "out of the blue".

Re: How to stop Excel Sheets from popping up in SAS Results Viewer window

Cynthia_sas — Wed, 06 Oct 2010 17:24:35 GMT

Hi:
I have some vague memory that "View Results as they are generated" has been around ever since ODS came out. How that choice was implemented for each version -- I think it's ALWAYS been checked by default. At least, when I do an install of SAS on my machine, I ALWAYS have to uncheck or turn off that option.

However, your folks may have done something different in the past when they were deploying SAS for installation -- so I can't speak to why it seemed to suddenly happen 'out of the blue'. You might pursue this with Tech Support if it really bothers you. Now that you know how to turn off the automatic open, you should be OK with how to toggle the automatic viewing on and off.

cynthia

topic Re: How to stop Excel Sheets from popping up in SAS Results Viewer window in ODS and Base Reporting

How to stop Excel Sheets from popping up in SAS Results Viewer window

Re: How to stop Excel Sheets from popping up in SAS Results Viewer window

Re: How to stop Excel Sheets from popping up in SAS Results Viewer window

Re: How to stop Excel Sheets from popping up in SAS Results Viewer window

Re: How to stop Excel Sheets from popping up in SAS Results Viewer window