PROC REPORT: HOW TO REMOVE SUBTOTALS SUMMARIZING ONLY 1 LINE

[nketata]

Hello, if you run the program below, it will give you a subtotal  by region. However   regions 12 and 13 only have 1 line in the final table; their subtotal is useless.

How can I remove the subtotal for regions 12 and 13 in the Proc Report ?  

DATA A;

REGION=11; PRODUCT="A"; SALES=200; output;

REGION=12; PRODUCT="A"; SALES=200; output;

REGION=13;PRODUCT="A"; SALES=400; output;

REGION=11;PRODUCT="B"; SALES=800; output;

REGION=11;PRODUCT="C"; SALES=1600; output;

run;

proc report data=A nowd style(summary)=Header out=B;

column REGION PRODUCT SALES;

define REGION /GROUP style(column)={just=r};

define PRODUCT /GROUP ;

define SALES / analysis sum "SALES";

break after REGION /summarize dol dul;

rbreak after/summarize;

compute after REGION;

call define('REGION','style','style=Header{pretext="Total for REGION" tagattr="Type:String"}');

endcomp;

format _numeric_ comma15.0;

run;

[SuryaKiran]

Try a datastep instead.

proc sort data=a;
by region;
data want;
format New_ReGION $15.;
do until(last.region);
set a end=eof ;
by region;
if first.region then do;
		sales_total=0;
		New_ReGION=put(region,3.);
		end;
else New_ReGION="";
sales_total+Sales;
overall_sales+Sales;
if not last.region then output;
end;
if not first.region then do;
		output;
		sales=sales_total;
		product="";
		New_ReGION=CATS("Total",PUT(REGION,3.));
		output;
		end;
if first.region then do;
		sales=sales_total;
		New_ReGION=CATS("Total",PUT(REGION,3.));
		output;
		end;

If eof then do;
	New_Region="Overall";
	Sales=Overall_sales;
	region="";Product="";
	output;
	end;
drop sales_total overall_sales region;
run;
 
proc print data=want noobs;
run;

[nketata]

Thank you for your reply. There is basically no solution inside the proc report.

[SASKiwi]

Conditional sub-totals need to use computed columns instead of BREAK statements. Does this post help?

https://communities.sas.com/t5/ODS-and-Base-Reporting/Proc-Report-Conditional-Subtotal/m-p/208201#M1...

[nketata]

Thank you for your reply. I saw that post but I was looking for a solution inside the proc report...Which does not exist apparently. 

[ballardw]

---

@nketata wrote:

Thank you for your reply. I saw that post but I was looking for a solution inside the proc report...Which does not exist apparently. 

---

No but since you have created a data set from the first proc report you could create a reduced data set with something like:

data want;
   set b;
   by notsorted region;
   if first.region then count=0;
   else count+1;
   if last.region and count=2 then delete;
   drop count;
run;

And print or possibly Proc report list with that data set.

If you are sending the output to a specific ODS destination then do the first proc report and the data step before the ODS destination is defined and then use the display procedure for the Want data set to get the results.

[SASKiwi]

Correct. You have to manually code it.