topic How to add a “cumulative percent” column without losing the visual features of the PROC TABULATE? in SAS Procedures

How to add a “cumulative percent” column without losing the visual features of the PROC TABULATE?

jaecheverrys — Wed, 25 Oct 2017 17:23:15 GMT

Hello everyone,

I am Julian and I need to produce a table that shows the layout you can see in this picture.

Using the following PROC TABULATE program in SAS Enterprise Guide 7.1 – 32 bits I’ve been able to produce all the columns except for the “Cumulative % of the Sum_Var2” one.

PROC TABULATE DATA= Julian.Mydata NOSEPS ;
         TITLE1 'Table 3';
   WEIGHT weight_var;
   CLASS  category_var / PRELOADFMT EXCLUSIVE;
   VAR Var1 Var2;
         TABLE (ALL='TOTAL' category_var=' '),
               (SUM='Sum_Var1'*Var1=' '*ALL=' '*F=COMMAX16.0
     SUM='Sum_Var2'*Var2=' '*F=dollar16.0
     MEAN='Mean_Var2'*Var2=' '*F=dollar16.0
     COLPCTSUM='% of the Sum_Var2'*Var2=' ')
      /RTS=25 PRINTMISS BOX='Categories' CONDENSE;
FORMAT category_var category_var.;
FOOTNOTE 'Source';
RUN;

Do you know a method to create that column without losing the visual features of the PROC TABULATE?

Thanks. I will be looking forward to your help.

Re: How to add a “cumulative percent” column without losing the visual features of the PROC TABULATE

ballardw — Wed, 25 Oct 2017 20:28:43 GMT

If you are attempting what I think you want then you can't do that in Tabulate as a single step. The percent calculations have to use either the n or sum of a table, row or column and cannot do sum or row1 and row2.

So you may have to presummarize everything. Without specific data and actual expectation I can't get more specific for suggestions on how.

Re: How to add a “cumulative percent” column without losing the visual features of the PROC TABULATE

mkeintz — Wed, 25 Oct 2017 21:25:18 GMT

You could use ODS OUTPUT to write the tabulate table to a dataset. Then create the cum_pct var from the PCT var, followed by a proc print. Here's a schematic:


ods output tabulate.report.table=MYTABLE;
proc tabulate data=mydata ;
  class ...;
  var ...;
  table .... ;
run;
ods output close;

/* Assume the pct var name is var2_pctsum_0_var2 */
data want;
  set mytable;
  cumpct+var2-pctsum_0_var2;
run;

proc print data=want;
....
run;