topic Re: Proc Report sum and average in SAS Procedures

Proc Report sum and average

sameer112217 — Sat, 07 Sep 2019 18:29:43 GMT

Hello I am writing a code but here is my difficulty

data sample;
length state $50;
input state$ city$ product$ selling_price revenue;
datalines;
Maharashtra Mumbai Soap 100 10
Maharashtra Mumbai Shampoo 200 20
Maharashtra Pune Soap 100 10
Maharashtra Pune Shampoo 200 20
Gujrat Baroda Come 100 2
Gujrat Baroda Pin 300 40
Gujrat Surat Come 100 2
Gujrat Surat Pin 400 10
Punjab Amritsar Pencil 200 3
Punjab Chandigarh Rubber 20 3
;
run;
proc print data=sample;
run;
data sample;
length state $50;
length zone $100;
set sample;
if state="Maharashtra" then target=500;
if state="Gujrat" then target=400;
if state="Punjab" then target=200;
if state="Maharashtra" then Zone="AB";
if state="Gujrat" then Zone="BC/MLA";
if state="Punjab" then Zone="BC/MLA";
run;
proc print data=sample;
run;

proc report data=sample;
column zone state selling_price target diff;
define zone/group;
define state/group;
define selling_price/sum;
define revenue/sum;
define target/mean;
define diff/computed;
compute diff;
diff=selling_price.sum-target.sum;
endcomp;

compute after zone;
zone=catx('',propcase(zone),'Subtotal');
line '';
endcomp;

/*break after state/summarize;*/
break after zone/summarize;
rbrEAK AFTER /SUMMARIzE DOL DUL;

compute after;
state= 'Total';
endcomp;
run;

I am getting below attached results. For Target I used target.mean in the diff in compute but I need sum in subtotal and total.

Re: Proc Report sum and average

sameer112217 — Sat, 07 Sep 2019 18:28:29 GMT

The subtotal and total I need is sum of the target. The target column numbers I had to add it in the above code, hence I had to use mean/average but while doing total I need the sum. Is there any way to do it. Also the zone subtotal using catx is showing shorter length. is there any way to increase it

Re: Proc Report sum and average

PaigeMiller — Sat, 07 Sep 2019 21:17:34 GMT

Please provide the actual code that you used that produced this report.

The code you provided is not the actual code used, as it produces an error.

 WARNING: revenue is not in the report definition.
 ERROR: The variable type of TARGET.SUM is invalid in this context.
 NOTE: The preceding messages refer to the COMPUTE block for diff.
 NOTE: Will not run due to compilation errors.
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: There were 10 observations read from the data set WORK.SAMPLE.
 NOTE: PROCEDURE REPORT used (Total process time):
       real time           0.10 seconds
       cpu time            0.04 seconds

Re: Proc Report sum and average

sameer112217 — Sun, 08 Sep 2019 10:44:37 GMT

Hello paigemiller, here i remove it

proc report data=sample;
column zone state selling_price target diff;
define zone/group;
define state/group;
define selling_price/sum;
define target/mean;
define diff/computed;
compute diff;
diff=selling_price.sum-target.mean;
endcomp;

compute after zone;
zone=catx('',propcase(zone),'Subtotal');
line '';
endcomp;

/*break after state/summarize;*/
break after zone/summarize;
rbrEAK AFTER /SUMMARIzE DOL DUL;

compute after;
state= 'Total';
endcomp;
run;

Re: Proc Report sum and average

PaigeMiller — Sun, 08 Sep 2019 14:11:48 GMT

@sameer112217 wrote:

Hello paigemiller, here i remove it

proc report data=sample;
column zone state selling_price target diff;
define zone/group;
define state/group;
define selling_price/sum;
define target/mean;
define diff/computed;
compute diff;
diff=selling_price.sum-target.mean;
endcomp;

compute after zone;
zone=catx('',propcase(zone),'Subtotal');
line '';
endcomp;

/*break after state/summarize;*/
break after zone/summarize;
rbrEAK AFTER /SUMMARIzE DOL DUL;

compute after;
state= 'Total';
endcomp;
run;

Your code is asking for means instead of sums. So SAS gives you what you asked for.

if that's not what you want, then you'd have to modify the code to get what you want. Can you show us (or tell us) the numbers you want to see, and how they are calculated?

Re: Proc Report sum and average

sameer112217 — Sun, 08 Sep 2019 18:29:34 GMT

Re: Proc Report sum and average

sameer112217 — Sun, 08 Sep 2019 18:30:31 GMT

i want average of target but sum in the subtotal and total

Re: Proc Report sum and average

PaigeMiller — Sun, 08 Sep 2019 21:02:11 GMT

@sameer112217 wrote:

i want average of target but sum in the subtotal and total

I have never seen a table where you show the average on some rows and sum for other rows in the same column. This seems like a bad idea to me.

Nevertheless, I have no idea how you can get PROC REPORT to show average on some rows in the column and sum on other rows in a column. Perhaps someone has a clever solution.

You could certainly create such a table of means and sums using PROC SUMMARY and data steps, so that the table is exactly what you want, and then simply have PROC REPORT display the resulting table.