Dear all,
if I have this code:
proc tabulate data=mydata format=6.0 style=[font_size=5pt just=c fontstyle=italic borderwidth=0 cellpadding=0 foreground=dimgray];
class var1 var2 var3 / order=data style=[textalign=center ] missing;
classlev var1 var2 var3 / style=[background=white font_weight=bold font_size=5pt foreground=dimgray cellpadding=2];
var Num_of_cases total_cases percentage /style=[background=white font_weight=bold font_size=5pt foreground=dimgray];
keyword all sum /style=[background=white font_weight=bold font_size=5pt foreground=dimgray];
table ( var1=' '*([style=[background=white foreground=dimgray font_weight=bold font_size=5pt]]) all='Totals per line')*
(num_of_cases='Number of cases' total_cases='sum of all cases' percentage='Percentages'*f=percent8.2) ,
(sum=' '* var2=''*var3=' ') / row=float nocontinued indent=1 rts=2 misstext=[label=" " ]
style=[cellpadding=3 font_size=5pt foreground=dimgray]
box=[label=' ' style=[background=white foreground=dimgray font_weight=bold font_size=5pt ]];
run;
If I use the "All=Totals per line" statement, I get the sum totals of all these three variables: num_of_cases, total_cases and percentage
at the bottom of the table. But I only need the sum totals of num_of_cases. Please help
and
Suggestion: Unless your question relates to actual use of style overrides and cell appearance for questions on the forum drop out all the appearances stuff as it just makes it hard to read the actual details related to the code submitted:
I think you are looking for this:
proc tabulate data=mydata ; class var1 var2 var3 / order=data missing; var Num_of_cases total_cases percentage /; keyword all sum /; table var1=' ' * (num_of_cases='Number of cases' total_cases='sum of all cases' percentage='Percentages'*f=percent8.2) All='Totals per line'*num_of_cases='Number of cases' , (sum=' '* var2=''*var3=' ') / row=float nocontinued indent=1 rts=2 misstext=[label=" " ] box=[label=' '] ; run;
When you use () to group things like (var1 all) *( thisvar thatvar anothervar) you forces both VAR1 and ALL to use the variables in the second (). Separate out the All and just use * with the desired variable(s).
Share some example data so we could test your code and help...
From what you shown my best guess would be to do something like:
proc tabulate data = sashelp.cars;
class origin cylinders;
var invoice mpg_city;
table (cylinders="Cylinder Inv." all="Tot. Inv.")*invoice=" "*sum=" "*f=dollar15.2
cylinders="Cylinder MPG"*mpg_city=" "*sum=" "
,origin
/ row=float
;
run;
Bart
@yabwon : using sashelp.cars as a sample data. I only want to output the totals per country only for the cylinders(at the bottom)
proc tabulate data=sashelp.cars format=6.0 style=[font_size=5pt just=c fontstyle=italic borderwidth=0 cellpadding=0 foreground=dimgray];
class make type origin / order=data style=[textalign=center ] missing;
classlev make type origin / style=[background=white font_weight=bold font_size=5pt foreground=dimgray cellpadding=2];
var cylinders horsepower MSRP /style=[background=white font_weight=bold font_size=5pt foreground=dimgray];
keyword all sum /style=[background=white font_weight=bold font_size=5pt foreground=dimgray];
table ( make=' '*([style=[background=white foreground=dimgray font_weight=bold font_size=5pt]]) all='Totals per country')*
(cylinders='cylinders' horsepower='horsepower' msrp='msrp') ,
(sum=' '* type=''*origin=' ') / row=float nocontinued indent=1 rts=2 misstext=[label=" " ]
style=[cellpadding=3 font_size=5pt foreground=dimgray]
box=[label=' ' style=[background=white foreground=dimgray font_weight=bold font_size=5pt ]];
run;
I guess something like this:
proc tabulate data=sashelp.cars format=6.0 style=[font_size=5pt just=c fontstyle=italic borderwidth=0 cellpadding=0 foreground=dimgray];
class make type origin / order=data style=[textalign=center ] missing;
classlev make type origin / style=[background=white font_weight=bold font_size=5pt foreground=dimgray cellpadding=2];
var cylinders horsepower MSRP /style=[background=white font_weight=bold font_size=5pt foreground=dimgray];
keyword all sum /style=[background=white font_weight=bold font_size=5pt foreground=dimgray];
table ( make=' '*([style=[background=white foreground=dimgray font_weight=bold font_size=5pt]]) /*all='Totals per country'*/ )*
(cylinders='cylinders' horsepower='horsepower' msrp='msrp')
cylinders='cylinders'*all='Totals per country' /* <- new code */,
(sum=' '* type=''*origin=' ') / row=float nocontinued indent=1 rts=2 misstext=[label=" " ]
style=[cellpadding=3 font_size=5pt foreground=dimgray]
box=[label=' ' style=[background=white foreground=dimgray font_weight=bold font_size=5pt ]];
run;
?
Bart
Suggestion: Unless your question relates to actual use of style overrides and cell appearance for questions on the forum drop out all the appearances stuff as it just makes it hard to read the actual details related to the code submitted:
I think you are looking for this:
proc tabulate data=mydata ; class var1 var2 var3 / order=data missing; var Num_of_cases total_cases percentage /; keyword all sum /; table var1=' ' * (num_of_cases='Number of cases' total_cases='sum of all cases' percentage='Percentages'*f=percent8.2) All='Totals per line'*num_of_cases='Number of cases' , (sum=' '* var2=''*var3=' ') / row=float nocontinued indent=1 rts=2 misstext=[label=" " ] box=[label=' '] ; run;
When you use () to group things like (var1 all) *( thisvar thatvar anothervar) you forces both VAR1 and ALL to use the variables in the second (). Separate out the All and just use * with the desired variable(s).
@ballardw Thankyou for that, that is what I wanted.
Just one more question, if I want to do row sums without the percentage rows. How does this work.
I tried but am not getting it. The percentage row are directly below the absolute values(rows)
@Anita_n wrote:
@ballardw Thankyou for that, that is what I wanted.
Just one more question, if I want to do row sums without the percentage rows. How does this work.
I tried but am not getting it. The percentage row are directly below the absolute values(rows)
I would have to see a bit of a worked example of exactly what you expect. Any ALL in the Column dimension is going to cross with everything in the rows. If the information is more important than having everything in a single table I would suggest a second Table statement (tabulate can have multiple tables created in one pass with the same Class and Var variables). Just leave out the bits you don't want in another table.
If the intent is to have a single table of a very specific structure you may need to consider either different data structure or perhaps look at a different procedure such as Proc Report (though report doesn't like multiple statistics in a single column) or both more data manipulation/summarizing before creating the report document and different report code.
One of the concerns I had when I read your code was the presence of a variable named Percentage in the first place. Summing or most statistics on a pre-calculated percentage value are either misleading, subject to misunderstanding or incorrect.
Can you provide a small example data set and what you are attempting to get with that data?
@ballardw: This is what I actually want : on the right handside I wish to calculate the sum totals per make only for cylinders and not for horsepower and mrsp
proc tabulate data=sashelp.cars ;
class make type origin / order=data missing;
var cylinders horsepower MSRP /;
keyword all sum /;
table make=' ' * (cylinders='cylinders' horsepower='horsepower' msrp='msrp')
cylinders='cylinders'*all='Totals per country' ,
(sum=' '* type=''*origin=' ') sum=''*all='Totals cylinders per make'
/ row=float nocontinued indent=1 rts=2 misstext=[label=" " ]
box=[label=' '] ;
run;
With tabulate doing the calculations the only way not to have the other variables "summed" is to exclude them from the table:
table make=' ' * (cylinders='cylinders' ) cylinders='cylinders'*all='Totals per country' , (sum=' '* type=''*origin=' ') sum=''*all='Totals cylinders per make' / row=float nocontinued indent=1 rts=2 misstext=[label=" " ] box=[label=' '] ;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.