topic Re: proc report: sum ,count,PCT count,PCT sum in SAS Programming

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

Ronein — Thu, 26 Sep 2019 04:56:08 GMT

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

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

Ksharp — Thu, 26 Sep 2019 11:55:49 GMT

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;

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

ballardw — Thu, 26 Sep 2019 14:26:57 GMT

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.

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

Ronein — Thu, 23 Jan 2020 19:25:27 GMT

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.

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

Ronein — Thu, 26 Sep 2019 18:16:23 GMT

Thank you.

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

column region sales,(n sum  pctn pctsum);

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

ballardw — Thu, 26 Sep 2019 20:14:25 GMT

@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.

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

Ronein — Thu, 26 Sep 2019 21:04:52 GMT

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

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

Ronein — Thu, 23 Jan 2020 19:26:04 GMT

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.;

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

Ksharp — Sat, 25 Jan 2020 07:23:03 GMT

Actually you could drop

define sales/sum;