Proc tabulate-create 4 tables with same columns headers

[Ronein]

Hello

This proc tabulate is not working 100% as I want .

I want to make the following changes:

1-Remove column header  "N" and keep only the header 'Total2'

2-I want to have only one row of columns headers (Instead of having it 4 times)

3- Titles of each table are not correct as I asked to do.why?

4-PCTSum total is not correct and should be 100.Why?

proc tabulate data=sashelp.cars  out=want_a1(drop=_type_ _page_ _table_) format=comma21. MISSING;
 class origin    Type /order=formatted ;
 VAR  invoice;
 Title 'Number of transactions ';
 table (origin='' All='Total')
      , (Type='' )*(n=''*f=comma21.) all='TOTAL2' / rts=10   box='Origin of Factory'  misstext='0';

 Title 'PCT of Number of transactions ';
  table (origin='' All='Total')
      , (Type='' )*(rowpctn=''*f=comma21.1) rowpctn*all='TOTAL2' / rts=10   box='Origin of Factory'  misstext='0';


Title 'Sum invoices';
  table (origin='' All='Total')
      , (Type='' )*(invoice=''*SUM=''*f=comma21.1) all='TOTAL2' / rts=10   box='Origin of Factory'  misstext='0';

Title 'PCT sum of invoices';
  table (origin='' All='Total')
      , (Type='' )*(invoice=''*rowpctsum=''*f=comma21.1) all='TOTAL2' / rts=10 box='Origin of Factory'  misstext='0';
run; 

[Reeza · Accepted solution]

---

@Ronein wrote:

Hello

1-Remove column header  "N" and keep only the header 'Total2'

---

all='TOTAL2'*N=''

2-I want to have only one row of columns headers (Instead of having it 4 times)

---

No idea

---

3- Titles of each table are not correct as I asked to do.why?

---

Title is only output once at the top. I suspect it's above the screenshot you've shown and the remainder are the automatic titles from SAS that need to be turned off. 

---

4-PCTSum total is not correct and should be 100.Why?

---

The column you've highlighted is the ALL which doesn't have PCTSUM applied to it, only the default N. If you add it, does it give you what you want?

  table (origin='' All='Total')
      , (Type='' )*(invoice=''*rowpctsum=''*f=comma21.1) all='TOTAL2'*rowpctsum / rts=10 box='Origin of Factory'  misstext='0';

[ballardw]

You can get text to appear before a table using the table option Pretext

 table (origin='' All='Total')
      , (Type='' )*(n=''*f=comma21.) all='TOTAL2'*n='' 
      / rts=10   box='Origin of Factory'  misstext='0'
      style=[pretext='Number of transactions']
;

Similar POSTTEXT will place text after the table similar to footnote and you can have both Pretext and Posttext for a single table. It only appears for that specific table. However, at least I haven't been able to, you don't have the text appearance options available that Title does.

I'm not sure exactly what you would expect for appearance with all the values suppressed in the columns. The value is what Tabulate uses to group values. You might get something that appears empty by setting the text color to the background color using a CLASSLEV statement.

Or perhaps go to a different approach such as summarizing the data and then using the Report Writing Interface in a data step. Maybe.

[Ronein]

Great and thanks,

However there are some problems:

1-This statement is not working and get an error

all='TOTAL2'*SUM=''

2-This statement is not working and get an error

all='TOTAL2'*rowpctn=''

title;
proc tabulate data=sashelp.cars  out=want_a1(drop=_type_ _page_ _table_) format=comma21. MISSING;
 class origin    Type /order=formatted ;
 VAR  invoice;

table (origin='' All='Total1')
      , (Type='Car Type' )*(n=''*f=comma21.) all='TOTAL2'*N='' / rts=10 box='Factory Location'  misstext='0'  style=[pretext='Number of transactions'];


table (origin='' All='Total1')
      , (Type='Car Type' )*(rowpctn=''*f=comma21.1) all='TOTAL2'*rowpctn='' / rts=10 box='Factory Location'  misstext='0'  style=[pretext='Tamhil Number of transactions'];


table (origin='' All='Total1')
      , (Type='Car Type' )*(invoice=''*SUM=''*f=comma21.1)  all='TOTAL2'*SUM=''  / rts=10 box='Factory Location'  misstext='0'  style=[pretext='SUM Haamadot MLS'];


 table (origin='' All='Total1')
      , (Type='Car Type' )*(invoice=''*rowpctsum=''*f=comma21.1)  all='TOTAL2'*rowpctsum=''   / rts=10 box='Factory Location'  misstext='0'  style=[pretext='Tamhil Sum Haamadot'];
run;