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;
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;
Thank you for your reply. There is basically no solution inside the proc report.
Conditional sub-totals need to use computed columns instead of BREAK statements. Does this post help?
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.
@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.
Correct. You have to manually code it.
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.
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.