topic Re: PROC TABULATE - Display Mean an Count in same column in ODS and Base Reporting

PROC TABULATE - Display Mean an Count in same column

lin39 — Wed, 20 Jul 2016 18:41:30 GMT

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!!

Re: PROC TABULATE - Display Mean an Count in same column

Reeza — Wed, 20 Jul 2016 18:46:23 GMT

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

post your current code.

Re: PROC TABULATE - Display Mean an Count in same column

ballardw — Wed, 20 Jul 2016 19:49:36 GMT

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;

Re: PROC TABULATE - Display Mean an Count in same column

Cynthia_sas — Wed, 20 Jul 2016 22:00:15 GMT

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;

Re: PROC TABULATE - Display Mean an Count in same column

Ksharp — Thu, 21 Jul 2016 01:47:11 GMT

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;

Re: PROC TABULATE - Display Mean an Count in same column

lin39 — Wed, 19 Oct 2016 21:19:46 GMT

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!

Re: PROC TABULATE - Display Mean an Count in same column

Cynthia_sas — Thu, 20 Oct 2016 03:58:24 GMT

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

Re: PROC TABULATE - Display Mean an Count in same column

lin39 — Fri, 21 Oct 2016 20:47:29 GMT

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