topic Re: PROC REPORT: HOW TO REMOVE SUBTOTALS SUMMARIZING ONLY 1 LINE in SAS Procedures

PROC REPORT: HOW TO REMOVE SUBTOTALS SUMMARIZING ONLY 1 LINE

nketata — Thu, 11 Oct 2018 17:47:34 GMT

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;

Re: PROC REPORT: HOW TO REMOVE SUBTOTALS SUMMARIZING ONLY 1 LINE

SuryaKiran — Thu, 11 Oct 2018 19:15:45 GMT

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;

Re: PROC REPORT: HOW TO REMOVE SUBTOTALS SUMMARIZING ONLY 1 LINE

SASKiwi — Thu, 11 Oct 2018 19:24:11 GMT

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#M13798

Re: PROC REPORT: HOW TO REMOVE SUBTOTALS SUMMARIZING ONLY 1 LINE

nketata — Thu, 11 Oct 2018 20:43:20 GMT

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

Re: PROC REPORT: HOW TO REMOVE SUBTOTALS SUMMARIZING ONLY 1 LINE

nketata — Thu, 11 Oct 2018 20:45:14 GMT

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.

Re: PROC REPORT: HOW TO REMOVE SUBTOTALS SUMMARIZING ONLY 1 LINE

ballardw — Fri, 12 Oct 2018 17:40:29 GMT

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

Re: PROC REPORT: HOW TO REMOVE SUBTOTALS SUMMARIZING ONLY 1 LINE

SASKiwi — Fri, 12 Oct 2018 22:21:43 GMT

Correct. You have to manually code it.