ODS EXCEL display "Total" label PROC report

[elsfy]

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

[PaigeMiller]

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

[elsfy]

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

[PaigeMiller]

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;

[elsfy]

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

[PaigeMiller]

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.

[elsfy]

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!

[PaigeMiller]

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.

[Cynthia_sas]

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/... I'm confused why the accepted solution to the previous posting is not working now.
Cynthia

[ballardw]

---

@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/... 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"?

[PaigeMiller]

---

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

[Cynthia_sas]

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

[elsfy]

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