proc report: sum ,count,PCT count,PCT sum

[Ronein]

Hello

From data set sashep.shoes I want to calculate for each "Region" the following 4 statistics:

sum  of sales
count  (number of rows)
percent of sum_sales from Total_sales
percent of counts from Total_count

The code is not correct.

proc report data=sashelp.shoes;
column region,sales(n sum  pctn pctsum);
define region /group style(column)=header;
define sales/sum;
define sales/ N 'count' f=comma6.;
define sales/sum 'sum_sales' f=dollar15.;
define sales/pctn  'percent from total counts' f=percent9.2;
define sales/ pctsum'percent from total sum_sales' f=percent9.2;

rbreak after /summarize  style (summary)=Header;
compute after;
region='Total';
endcomp;
run;

errors:

ERROR: A GROUP variable appears above or below other report items.
Name Usage
-------------------------------- --------
Region GROUP
Sales ANALYSIS
ERROR: There is a statistic but no ANALYSIS usage associated
with the column defined by the following elements.
Name Usage
-------------------------------- --------
sum STATISTIC
ERROR: There is a statistic but no ANALYSIS usage associated
with the column defined by the following elements.
Name Usage
-------------------------------- --------
pctsum STATISTIC

[Ksharp]

I think you should switch into PROC TABULATE for these summary statistics.

proc report data=sashelp.shoes nowd;
column region sales,(n sum  pctn pctsum);
define region /group style(column)=header;
define sales/sum;
define n/ 'count' f=comma6.;
define sum/ 'sum_sales' f=dollar15.;
define pctn / 'percent from total counts' f=percent9.2;
define pctsum/ 'percent from total sum_sales' f=percent9.2;

rbreak after /summarize  style (summary)=Header;
compute after;
region='Total';
endcomp;
run;

[Ronein]

Thank you very much.

As I understand you don't recommend using proc report for this request(calculate sum,N,PCT,PCTSUM).

May you please send code with proc tabulate that calculate these statistics.

[Ronein]

I don't understand...In the required output should have 5 columns( region,No_Rows,PCT_Rows,Sales_SUM,PCT_sales_SUM).

Why in the proc report code there are 6 Define statements if we need only 5 columns?

Why are there 2 Define statements for sum of sales?

define sales/sum;

define sum/ 'sum_sales' f=dollar15.;

[Ksharp]

Actually you could drop

define sales/sum;

[ballardw]

To control the labels as you are attempting you would need to use alias names for each column:

proc report data=sashelp.shoes;
column region  sales=salesn sales sales=salespct sales=salespctsum;
define region /group style(column)=header;
define salesn/ N 'count' f=comma6.;
define sales/sum 'sum_sales' f=dollar15.;
define salespct/pctn  'percent from total counts' f=percent9.2;
define salespctsum/ pctsum'percent from total sum_sales' f=percent9.2;

rbreak after /summarize  style (summary)=Header;
compute after;
region='Total';
endcomp;
run;

There is an example in the Proc Report documentation for Using Aliases to Obtain Multiple Statistics for the Same Variable, which is what you are attempting.

I think that you intended something like:

column region sales,(n sum  pctn pctsum);

The comma after region was forcing the "statistics" into a different role as there was no way to determine which variable you requested sum, pctn and pctsum statics for.

but all of the statistics use the default format for sales, which really works poorly with percentage calculations.

[Ronein]

Thank you.

I didn't understand....did you mean that we cannot use the way of writing 

column region sales,(n sum  pctn pctsum);

 ?

[ballardw]

---

@Ronein wrote:

Thank you.

I didn't understand....did you mean that we cannot use the way of writing 

column region sales,(n sum  pctn pctsum);

 ?

---

You can use it that way. But run it against the data set. The values for pctn and pctsum will look odd because of the formats assigned.

[Ronein]

Can you please show the code with "column region sales,(n sum pctn pctsum);"?

I want to see if it works and also to see the difference in comparison to the code you provided before