The SAS Output Delivery System and reporting techniques

PROC TABULATE - Display Mean an Count in same column

Reply
Occasional Contributor
Posts: 9

PROC TABULATE - Display Mean an Count in same column

Hi all,

 

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

proc_tab1.PNG

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

 

 

Grand Advisor
Posts: 16,343

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

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

 

post your current code. 

 

 

Grand Advisor
Posts: 9,740

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

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;

SAS Super FREQ
Posts: 8,645

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

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:

 

make_combined_cell.png

 

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;

 

 

Grand Advisor
Posts: 9,325

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

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;

x.png

Occasional Contributor
Posts: 9

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

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!

 

 

SAS Super FREQ
Posts: 8,645

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

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
Occasional Contributor
Posts: 9

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

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

Post a Question
Discussion Stats
  • 7 replies
  • 305 views
  • 4 likes
  • 5 in conversation