Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- ODS and Base Reporting
- /
- How to stop Excel Sheets from popping up in SAS Re...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2010 12:41 PM

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") ;

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") ;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to chandler

10-04-2010 02:38 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

10-06-2010 01:17 PM

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".

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to chandler

10-06-2010 01:24 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to chandler

10-04-2010 02:39 PM

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.

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.