Add new row with sum

newbi — Tue, 22 Mar 2011 20:16:06 GMT

Is there a way in SAS i can create row after each region for regional total ?

For example: I have table with following columns:

Region Rep Sales
A 1 $100
A 2 $500
B 3 $200
B 4 $1000

I would like the results to be:

Region Rep Sales
A 1 $100
A 2 $500
Total $600
B 3 $200
B 4 $1000
Total $1200

Thanks

Re: Add new row with sum

newbi — Tue, 22 Mar 2011 21:11:23 GMT

I figure it out. Thanks

Re: Add new row with sum

Cynthia_sas — Tue, 22 Mar 2011 22:32:45 GMT

Hi:
You can use the SUM statement in PROC PRINT or use PROC REPORT with a BREAK statement. I suppose you could even do this in an SQL query, but I'd stick with PRINT or REPORT first. Consider this example that uses SASHELP.SHOES. Report 1 and Report 2 are "detail" reports because they show every observation for Canada and Pacific regions, with subtotals between each region. On the other hand, Report 3 is a "summary" report because it collapses the Canada observations in groups by region and product and then has a subtotal for the region groups.

cynthia
[pre]
** only get 2 regions;
proc sort data=sashelp.shoes out=shoes;
by region product;
where region in ('Canada', 'Pacific');
run;

ods listing;
ods html file='c:\temp\region_total.html' style=sasweb;

proc print data=shoes;
var region product sales;
sum sales;
sumby region;
by region;
title '1) Proc Print Detail Report with Subtotals';
run;

proc report data=shoes nowd
style(summary)=Header;
column region product sales;
define region / order;
define product/order;
define sales / sum;
break after region / summarize page;
compute after region;
region = 'Total';
endcomp;
title '2) Proc Report Detail Report with Subtotals';
run;

proc report data=shoes nowd
style(summary)=Header;
column region product sales;
define region / group;
define product/group;
define sales / sum;
break after region / summarize;
compute after region;
region = 'Total';
endcomp;
title '3) Proc Report Summary Report with Subtotals';
run;

ods _all_ close;
[/pre]

topic Add new row with sum in SAS Programming

Add new row with sum

Re: Add new row with sum

Re: Add new row with sum