BookmarkSubscribeRSS Feed
nketata
Obsidian | Level 7

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;

6 REPLIES 6
SuryaKiran
Meteorite | Level 14

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;
Thanks,
Suryakiran
nketata
Obsidian | Level 7

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

SASKiwi
Opal | Level 21

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
Obsidian | Level 7

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
Super User

@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
Opal | Level 21

Correct. You have to manually code it.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1165 views
  • 4 likes
  • 4 in conversation