PROC TABULATE - Display Mean an Count in same column

[lin39]

Hi all,

I have a table that looks almost like the one below:

However, client wants the N and Mean to be in the same column, and have Mean displayed below N.  I'm scratching my head. Is there any way that SAS could accomplish this? i.e.,

Female        ||  Male

======================

3                  ||  3

$44,789       || $52,507

======================

etc.etc...

Thanks so much!!

[Reeza]

Yes, it depends on where you put the n/mean calc. Move them to after comma?

post your current code. 

[ballardw]

Show your code so we could use the same the variables.

The difference would be (guessing your current table statement)

table education*(enrollment all='All'), sex*(n mean);

change to

table education*(enrollment all='All')*(n mean), sex;

[Cynthia_sas]

Hi:

  Frequently, my students ask for this to produce standard "demographic" tables where they want to see something like this:

N (Mean) or N (Min - Max) or N (PctN%)

where separate statistics are formatted and presented in one cell, like this:

Compare the way the TABULATE (#1) output looks with the "combined" cell in the PROC REPORT (#2) output. This was done by making a character variable in PROC REPORT as a COMPUTED item and then formatting the N and the Mean and concatenating them with punctuation. Code is below -- if this is what you mean. If this is not what you mean, then post your TABULATE data and your code and provide more information about your desired result format (listing, HTML, RTF, PDF, Excel, etc). My output in the screen shot is the default HTML output.

cynthia

The code:

title;
proc tabulate data=sashelp.shoes;
  title '1) With PROC TABULATE -- combined cell is not possible';
  where region in ('Asia', 'Canada','Pacific') and
        product in ('Boot', 'Slipper' );
  class product region;
  var sales;
  table region * (product all),
        sales*(n mean);
run;

proc report data=sashelp.shoes;
  title '2) With PROC REPORT make combined cell';
  where region in ('Asia', 'Canada','Pacific') and
        product in ('Boot', 'Slipper' );
  column region product  sales sales=slmean combined;
  define region / group style(column) = Header;
  define product /group style(column)=Header;
  define sales / n f=4.0 noprint;
  define slmean / mean noprint;
  define combined / computed f=$50. 
         style(column)={just=r};
  compute combined / character length=50;
    combined = put(sales.n,4.0)||'  ('||put(slmean,dollar14.)||')';
  endcomp;
  break after region / summarize style=Header;
  compute after region;
    region = ' ';
    product = 'All';
  endcomp;
run;

[Ksharp]

Modify Cynthia's code a little bit .

title;
proc tabulate data=sashelp.shoes;
  title '1) With PROC TABULATE -- combined cell is not possible';
  where region in ('Asia', 'Canada','Pacific') and
        product in ('Boot', 'Slipper' );
  class product region;
  var sales;
  table region * (product all),
        sales*(n mean);
run;
ods escapechar='~';
proc report data=sashelp.shoes;
  title '2) With PROC REPORT make combined cell';
  where region in ('Asia', 'Canada','Pacific') and
        product in ('Boot', 'Slipper' );
  column region product  sales sales=slmean combined;
  define region / group style(column) = Header;
  define product /group style(column)=Header;
  define sales / n f=4.0 noprint;
  define slmean / mean noprint;
  define combined / computed f=$50. 
         style(column)={just=r};
  compute combined / character length=50;
    combined = put(sales.n,4.0)||' ~n ('||put(slmean,dollar14.)||')';
  endcomp;
  break after region / summarize style=Header;
  compute after region;
    region = ' ';
    product = 'All';
  endcomp;
run;

[lin39]

Hi KSharp,  thank you so much.  I appreciate yours and Cynthia's code. 

My problem is, I need to display N and mean of not only one var (i.e., Sales), but also 10 other variables like income, GPA, SAT.  If I use the method suggested, I need to define the N for each variable, then Mean, then define a combined field and a computing logic for the combined field for every var.  

i.e.,

define MONEY / n f=4.0 noprint;
define SAT / n f=4.0 noprint;

define MONEYmean / mean noprint;
define SATmean / mean noprint;

define MONEYc / computed f=$50. style(column)={just=c};
define SATc / computed f=$50. style(column)={just=c};

compute MONEYc / character length=50; MONEYc = put(MONEY.n,4.0)||' ~n ('||put(MONEYmean, PERCENT9.1)||')'; endcomp;

etc etc for 10 vars.

That is a lot of code and dfficult to manage.

What's your suggestion????

Thank you!

[Cynthia_sas]

My suggestion is to use a DATA step program and possibly a SAS macro definition to create the concatenated character variable with the N and the Mean in the DATA step so you can simplify the logic in the PROC REPORT. Look at Example 1 in this paper: http://www2.sas.com/proceedings/forum2008/173-2008.pdf on page 9.

You will find the programs for the original paper here: http://support.sas.com/rnd/papers/#SGF2008 just scroll to the 2008 section and look for the paper title to find the zip file.

cynthia

[lin39]

Thanks so much, Cynthia. I will give it a try.