topic Re: ODS EXCEL display "Total" label PROC report in SAS Programming

ODS EXCEL display "Total" label PROC report

elsfy — Fri, 13 May 2022 12:12:27 GMT

Hi,

In the "COMPUTE AFTER" instruction, i want to display in the summary row "TOTAL" as a label. It works with proc report result & html output but when i use ODS EXCEL, the cell is empty.


proc report data= a 
	style(column)={backgroundcolor=white cellwidth=2 bordercolor=white  just=r width=150 font_weight=bold tagattr='format:###,###,###,###,###0') ; 

columns ("^{style[backgroundcolor=MediumVioletRed color=white]Category}" var1 var2 var3 var4 var5 var6 var7) 
;
define var1 / display;
define var2 / display ;
define var3  / display ;
define var4 / display  ; 
define var5 / display  ;  
define var6 / display  ;  
define var7 / display  ;  

rbreak after / summarize ; 
 
compute after ;  
call define ('var4','style','style={bordertopcolor=lightgrey borderbottomcolor=lightgrey}') ; 
if _break_ ne ' ' then call define ('var5','style','style=[pretext="Total" bordertopcolor=lightgrey borderbottomcolor=lightgrey]') ;
endcomp ; 
run ;

in the yellow cell in excel, it must be written "Total".

Re: ODS EXCEL display "Total" label PROC report

PaigeMiller — Fri, 13 May 2022 12:26:10 GMT

This exact question is answered here: https://communities.sas.com/t5/SAS-Programming/PROC-REPORT-can-you-place-descriptive-text-in-a-BREAK-or-RBREAK/td-p/431401

Re: ODS EXCEL display "Total" label PROC report

elsfy — Fri, 13 May 2022 12:42:39 GMT

Hi,

doing this, it's not working :

proc report data= a 
	style(column)={backgroundcolor=white cellwidth=2 bordercolor=white  just=r width=150 font_weight=bold tagattr='format:###,###,###,###,###0') ; 

columns ("^{style[backgroundcolor=MediumVioletRed color=white]Category}" var1 var2 var3 var4 var5 var6 var7) 
;
define var1 / display;
define var2 / display ;
define var3  / display ;
define var4 / display  ; 
define var5 / display  ;  
define var6 / display  ;  define var7 / display  ;  
rbreak after / summarize ; 
 
compute after ;  
call define ('var4','style','style={bordertopcolor=lightgrey borderbottomcolor=lightgrey}') ; 
endcomp ; 
compute after var5 ; 
var5 = "TOTAL' ; 
endcomp ;
run ;

With this error message : "ERROR: You can only BREAK on GROUPing and ORDERing variables."

Re: ODS EXCEL display "Total" label PROC report

PaigeMiller — Fri, 13 May 2022 12:57:17 GMT

That's because you must have a GROUP or ORDER variable to use BREAK, and you don't have that. So maybe yours is not the exact same problem.

Here is an example that should work without a GROUP or ORDER variable.

data class;
 length sex $ 40;
 set sashelp.class;
 sex=ifc(sex='F','Female','Male');
run;
proc report data=class nowd; 
    columns sex height weight;
    define sex/display;
    define height/display mean format=10.1;
    define weight/display mean format=10.1;
    rbreak after/summarize;
    	compute after;
	  sex='Mean';
	endcomp;
run;

Re: ODS EXCEL display "Total" label PROC report

elsfy — Fri, 13 May 2022 13:07:02 GMT

It's not working not even on the HTML proc report output

Re: ODS EXCEL display "Total" label PROC report

PaigeMiller — Fri, 13 May 2022 13:09:17 GMT

IMPORTANT CONCEPT: saying it doesn't work and providing no additional information never helps. The next question is always to show us the LOG and/or the incorrect output.

Re: ODS EXCEL display "Total" label PROC report

elsfy — Fri, 13 May 2022 13:49:38 GMT

So i tried this but it returns an empty cell in the final output.

proc report data= a style(column)={backgroundcolor=white cellwidth=2 bordercolor=white just=r width=150 font_weight=bold tagattr='format:###,###,###,###,###0') ; columns ("^{style[backgroundcolor=MediumVioletRed color=white]Category}" var1 var2 var3 var4 var5 var6 var7) ; define var1 / display; define var2 / display ; define var3 / display ; define var4 / display ; define var5 / display ; define var6 / display ; define var7 / display ; rbreak after / summarize ; compute after ; call define ('var4','style','style={bordertopcolor=lightgrey borderbottomcolor=lightgrey}') ; var5 = "TOTAL"; endcomp ; run ;

Thanks!

Re: ODS EXCEL display "Total" label PROC report

PaigeMiller — Fri, 13 May 2022 14:35:49 GMT

I guess I am going to need to see a portion of data set A

Please provide the data set as SAS data step code, which you can type in yourself or using these instructions. Do not provide the data in any other form.

Minor detail which probably doesn't matter in this case, but we prefer it if you provide the LOG when the code doesn't work.

Re: ODS EXCEL display "Total" label PROC report

Cynthia_sas — Fri, 13 May 2022 15:12:02 GMT

Hi:
And, in this previous posting, I showed how to get Total and how to hide the cell borders: https://communities.sas.com/t5/SAS-Programming/Proc-report-Merge-grand-total-cells-or-hide-some/m-p/811888#M320291 I'm confused why the accepted solution to the previous posting is not working now.
Cynthia

Re: ODS EXCEL display "Total" label PROC report

ballardw — Fri, 13 May 2022 15:28:45 GMT

@Cynthia_sas wrote:
Hi:
And, in this previous posting, I showed how to get Total and how to hide the cell borders: https://communities.sas.com/t5/SAS-Programming/Proc-report-Merge-grand-total-cells-or-hide-some/m-p/811888#M320291 I'm confused why the accepted solution to the previous posting is not working now.
Cynthia

A guess is that all of the OP variables are numeric so the column doesn't like "Total"?

Re: ODS EXCEL display "Total" label PROC report

PaigeMiller — Fri, 13 May 2022 16:46:35 GMT

@ballardw wrote:

@Cynthia_sas wrote:
Hi:
And, in this previous posting, I showed how to get Total and how to hide the cell borders: https://communities.sas.com/t5/SAS-Programming/Proc-report-Merge-grand-total-cells-or-hide-some/m-p/811888#M320291 I'm confused why the accepted solution to the previous posting is not working now.
Cynthia

A guess is that all of the OP variables are numeric so the column doesn't like "Total"?

Good guess, I like it. If so, the user could assign it a value like -9999 and then format the variable such that -9999 appears as "Total"

Re: ODS EXCEL display "Total" label PROC report

Cynthia_sas — Mon, 16 May 2022 13:02:00 GMT

Hi: That's one workaround. Using PRETEXT is another workaround (but that isn't reliable in all destinations) and just making a COMPUTED variable that's character is the method I usually use:

data fakedata;
  set sashelp.shoes(obs=10);
  var1 = Region;
  var2 = Subsidiary;
  var3 = round(divide(inventory,10000),1.);
  var4 = sales;
  var5 = returns;
run;

title;

  
ods excel file='c:\temp\numborders.xlsx';
proc report data=fakedata
  style(summary)=Header{font_weight=bold font_size=2 background=lightgrey just=r };
  column var1 var2 var3 alt3 var4 var5;
  define var1 / display;
  define var2 / display;
  define var3 / display f=5.0 noprint;
  define alt3 / computed f=$5. style(column)={just=r};
  define var4 / f=dollar9.0;
  define var5 / f=dollar9.0;
  rbreak after / summarize;
  compute alt3 / character length=5;
    alt3 = put(var3,5.0);
  endcomp;
  compute after;
     call define('var1','style','style={borderrightcolor=lightgrey}');
     call define('var2','style','style={borderleftcolor=lightgrey borderrightcolor=lightgrey}');
     call define('alt3','style','style={borderleftcolor=lightgrey}');
	 alt3 = 'Total';
  endcomp;
run;
ods excel close;

Cynthia

Re: ODS EXCEL display "Total" label PROC report

elsfy — Mon, 16 May 2022 13:41:46 GMT

Hi, thank you for your request. Would you mind giving an example of how can i do that ?