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".
This exact question is answered here: https://communities.sas.com/t5/SAS-Programming/PROC-REPORT-can-you-place-descriptive-text-in-a-BREAK...
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."
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;
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.
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!
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 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"?
@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.
CynthiaA 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"
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
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!
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.