Formatting grand totals in datasets

[Q1983]

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_

[art297]

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

[Tom]

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;

[Cynthia_sas]

Hi:
This sounds to me like a REPORT and not a data set. You could do this with either TABULATE or REPORT. What will you do with your data set when you have it?
cynthia

[stat_sas]

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;

[Cynthia_sas]

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