BookmarkSubscribeRSS Feed
elsfy
Quartz | Level 8

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 ;

isgnr_0-1652443660906.png

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

12 REPLIES 12
elsfy
Quartz | Level 8

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
Diamond | Level 26

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;

 

--
Paige Miller
elsfy
Quartz | Level 8
It's not working not even on the HTML proc report output
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
elsfy
Quartz | Level 8

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
Diamond | Level 26

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.

--
Paige Miller
Cynthia_sas
SAS Super FREQ
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
Super User

@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
Diamond | Level 26

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

--
Paige Miller
Cynthia_sas
SAS Super FREQ

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
Quartz | Level 8
Hi, thank you for your request. Would you mind giving an example of how can i do that ?

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1796 views
  • 3 likes
  • 4 in conversation