topic Re: Multiple Statistics in proc report in ODS and Base Reporting

Multiple Statistics in proc report

SidS — Mon, 07 Feb 2011 08:09:52 GMT

Hello I want to create a report in this form:
People GDP
Russia
India
China
USA

Mean
Median

I have about 10 different variables like people and GDP and I need mean and median for each after the observations have been listed. In case you are familiar with this document, I can use the sample code shown in http://www2.sas.com/proceedings/sugi30/259-30.pdf. But using the method there would mean creating 20 different country variables, 20 new mean and median variables and then writing the compute function 20 times. There has to be simpler way! Can somebody please tell me how to create this simple report given the data points for each country?

Thanks so much!
Sid

Re: Multiple Statistics in proc report

Peter_C — Mon, 07 Feb 2011 10:24:24 GMT

Sid
how many rows in your input?,
How many columns?
Is PROC REPORT the only solution you will accept? (if so, then why?)
Where do you want results (text, table, html, excel, pdf) ?
hth
peterC

Re: Multiple Statistics in proc report

Cynthia_sas — Mon, 07 Feb 2011 16:11:16 GMT

Hi:
As Peter suggests, other procedures (such as PROC TABULATE) may be better suited to your report, however, there is a second method to get more extra summary lines at the bottom of a PROC REPORT break group.

For example, take a look at SASHELP.SHOES. If I wanted to create this report (shown only for Asia):
[pre]
Region Product Inventory Sales Returns
Asia Boot $9,576 $1,996 $80
Men's Dress $20,831 $3,033 $52
Sandal $15,087 $3,230 $120
Slipper $16,075 $3,019 $127
Women's Casual $16,251 $5,389 $185
Boot $160,589 $60,712 $1,296
Men's Casual $2,176 $11,754 $833
Men's Dress $251,803 $116,333 $2,443
Sandal $21,483 $4,978 $105
Slipper $469,007 $149,013 $2,941
Sport Shoe $455 $937 $10
Women's Casual $36,576 $20,448 $790
Women's Dress $140,628 $78,234 $1,891
Sport Shoe $15,602 $1,155 $22
Sum $1,176,139 $460,231 $10,895
Mean $84,010 $32,874 $778
Median $18,541 $5,184 $156
[/pre]

I would need to create 2 "extra" break variables and only have 2 more COMPUTE blocks added to my PROC REPORT code -- and the Mean and Median break lines would get automatically added for every unique value of region (as long as I created by 2 "extra" break variables correctly.

Is this report close to what you had in mind???

cynthia

Re: Multiple Statistics in proc report

SidS — Mon, 07 Feb 2011 22:27:23 GMT

Peter,
I do not need to use proc report. If there is an easier way, please let me know as I am still learning SAS. Preferably, I would like the output to be in latex. But that seems to bring about a whole bunch of other problems, so I will be happy with a csv or xls output for now. I have about 150 rows.

Cynthia,
Yes I need the output just like your report. I actually have groups by continent as well which I forgot to mention. How did you create it?

When is proc tabulate preferable to proc report?

Thanks,
Sid

Re: Multiple Statistics in proc report

Cynthia_sas — Tue, 08 Feb 2011 01:57:07 GMT

Hi:
I created my report with PROC REPORT. I could also have used TABULATE. The difference between REPORT and TABULATE is going to come down to how comfortable you are with the syntax of each.

REPORT is more verbose and takes more statements to achieve the same thing that TABULATE can do in a very elegant and almost algebraic syntax. On the other hand, TABULATE cannot skip lines between groups and TABULATE does not have the COMPUTE block. REPORT has the NOPRINT option (which allows you to hide columns, but still use them on the REPORT -- which is what I needed for this report). REPORT also has aliasing techniques -- which allows you to use the same variable multiple times to get different statistics.

So, you decide. The code to create the table with PROC TABULATE is first, followed the the code for PROC REPORT. The ODS statements use a variety of styles for a variety of destinations. I have no way of knowing what the LaTeX output will look like because I don't exactly know how to compile or look at a LaTeX file -- I only know that I can create it with ODS.

cynthia
[pre]
** Make "fake" variables for PROC REPORT;
** also subset data with WHERE statement;
** TABULATE will use the subset of data;
** REPORT needs the extra variables -- to be unique and the same for each region;
data shoes;
set sashelp.shoes;
by region;
where region in ('Asia', 'Canada', 'Pacific');
retain regcntr;
if first.region then regcntr +1;
othrcntr = regcntr*100;
run;

ods listing close;
ods csvall file='c:\temp\compare.csv';
ods pdf file='c:\temp\compare.pdf';
ods rtf file='c:\temp\compare.rtf' style=journal;
ods tagsets.excelxp file='c:\temp\compare_xp.xls' style=sasweb;
ods html file='c:\temp\compare.html' style=sasweb;
ods latex file='c:\temp\compare.tex' style=printer;

proc tabulate data=shoes f=comma14.2;
title 'TABULATE approach';
class region product;
var inventory sales returns;
table (region * (product all*{s={font_weight=bold}} mean*{s={font_weight=bold}} median*{s={font_weight=bold}})),
inventory sales returns
/ row=float;
keylabel sum=' '
all='Sum';
run;

proc report data=shoes nowd
style(summary)=Header;
title 'REPORT approach';
column othrcntr regcntr region product inventory sales returns
inventory=imed sales=smed returns=rmed
inventory=iavg sales=savg returns=ravg ;
define othrcntr / group noprint;
define regcntr /group noprint;
define region / group style(column)=Header;
define product / group style(column)=Header;
define inventory/ sum 'Inventory';
define sales / sum 'Sales';
define returns / sum 'Returns';

define imed/ median 'Median Inv' noprint;
define smed / median 'Median Sales' noprint;
define rmed / median 'Median Ret' noprint;

define iavg/ mean 'Avg Inv' noprint;
define savg / mean 'Avg Sales' noprint;
define ravg / mean 'Avg Ret' noprint;
break after othrcntr / summarize;
break after regcntr / summarize;
break after region / summarize;

compute after othrcntr;
region = 'Median';
inventory.sum = imed;
sales.sum = smed;
returns.sum = rmed;
line ' ';
endcomp;
compute after regcntr;
region = 'Mean';
inventory.sum = iavg;
sales.sum = savg;
returns.sum = ravg;
endcomp;
compute after region;
region = 'Sum';
endcomp;

run;

ods _all_ close;
[/pre]

Re: Multiple Statistics in proc report

Ksharp — Mon, 21 Feb 2011 09:39:32 GMT

Or maybe you will like 'line' statement.

[pre]

data shoes;
set sashelp.shoes;
by region;
where region in ('Asia', 'Canada', 'Pacific');
run;

ods html file='c:\temp\compare.html' style=sasweb;
proc report data=shoes nowd ;
title 'REPORT approach';
column region product sales sales=mean_sales sales=median_sales;
define region / group ;
define product / order ;
define sales / display 'Sales';
define mean_sales/analysis mean noprint;
define median_sales/analysis median noprint;

compute after region / style={font_weight=bold};
line 'Mean :' @20 mean_sales dollar18.;
line 'Median:' @20 median_sales dollar18.;
endcomp;
run;
ods html close;

[/pre]

Ksharp