BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Anita_n
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

 

View solution in original post

8 REPLIES 8
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Anita_n
Pyrite | Level 9

@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;
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



ballardw
Super User

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

 

Anita_n
Pyrite | Level 9

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

ballardw
Super User

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

Anita_n
Pyrite | Level 9

@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;
ballardw
Super User

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1516 views
  • 1 like
  • 3 in conversation