data sales;
input @1 region $char8. @10 repid 4. @15 amount 10. ;
format amount dollar12.;
datalines;
NORTH 1001 1000000
NORTH 1002 1100000
NORTH 1003 1550000
NORTH 1008 1250000
NORTH 1005 900000
SOUTH 1007 2105000
SOUTH 1010 875000
SOUTH 1012 1655000
EAST 1051 2508000
EAST 1055 1805000
;
run;
proc sort data=sales;
by region;
run;
proc means noprint data= sales;/*This provides a grand total only*/
output out= sales_tot sum= ;
run;
data sales_tot_1;
set sales sales_tot; /*I am including the details and sum at bottom by combining both datasets*/
run;
This gives me the following
REGION REPID AMOUNT _TYPE_ _FREQ_
EAST 1051 $2,508,000
EAST 1055 $1,805,000
NORTH 1055 $1,000,000
NORTH 1002 $1,100,000
NORTH 1003 $1,550,000
NORTH 1008 $1,250,000
NORTH 1005 $900,000
SOUTH 1007 $2,105,000
SOUTH 1010 $875,000
SOUTH 1012 $1,655,000
10154 $14,748,000 0 10
How can I format this so I can
1. Display the text 'Grand Total' under the column REGION
2. Assign a blank under REPID
3. Remove _TYPE_ and _FREQ_
A couple of small changes to your code will accomplish what you want:
data sales; informat region $8.; input region repid amount; format amount dollar12.; datalines; NORTH 1001 1000000 NORTH 1002 1100000 NORTH 1003 1550000 NORTH 1008 1250000 NORTH 1005 900000 SOUTH 1007 2105000 SOUTH 1010 875000 SOUTH 1012 1655000 EAST 1051 2508000 EAST 1055 1805000 ; proc sort data=sales; by region; run; proc means noprint data= sales nway;/*This provides a grand total only*/ var amount; output out= sales_tot (drop=_:) sum= ; run; data sales_tot_1; length region $11; set sales sales_tot (in=tot); if tot then region='Grand Total'; run;
Art, CEO, AnalystFinder.com
You probably want to produce a REPORT and not a DATASET, but let's see what we can do.
First tell proc means/summary which variables you want to sum and to drop the automatic variables.
proc summary data=sales nway ;
var amount ;
output out= sales_tot (drop=_type_ _freq_) sum= ;
run;
Then when you combine the datasets figure out what records are coming from the summary dataset and adjust REGION. You will probably need to make sure REGION is long enough to store 'GRAND TOTAL'.
data sales_tot_1 ;
length region $%length(GRAND TOTAL);
set sales sales_tot (in=in2);
if in2 the region='GRAND TOTAL';
run;
Hi,
Another way using proc sql
proc sql;
create table sales_tot_1 as
select * from sales
union all
select "Grand_Total", . , sum(amount) as amount from sales;
quit;
Hi:
You'll need to make REGION a length of 11 to fit the "Grand Total" in the column, but assuming you do that, then this PROC REPORT code does what you want:
proc report data=sales;
column region repid amount;
define region / group;
define repid / group;
define amount / sum;
rbreak after / summarize;
compute after;
region = 'Grand Total';
endcomp;
run;
The repetitious display of REGION is a side effect of GROUP usage (there is a way to work around that), but another nice side effect is that REPID is automatically blanked out on the summary line.
cynthia
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.