DATA Step, Macro, Functions and more

Add new row with sum

Reply
SAS Employee
Posts: 73

Add new row with sum

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
SAS Employee
Posts: 73

Re: Add new row with sum

I figure it out. Thanks
SAS Super FREQ
Posts: 8,868

Re: Add new row with sum

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]
Ask a Question
Discussion stats
  • 2 replies
  • 913 views
  • 0 likes
  • 2 in conversation