BookmarkSubscribeRSS Feed
SidS
Calcite | Level 5
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
5 REPLIES 5
Peter_C
Rhodochrosite | Level 12
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
Cynthia_sas
SAS Super FREQ
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
SidS
Calcite | Level 5
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
Cynthia_sas
SAS Super FREQ
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]
Ksharp
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1938 views
  • 0 likes
  • 4 in conversation