topic Re: Formatting grand totals in datasets in SAS Programming

Formatting grand totals in datasets

Q1983 — Tue, 26 Dec 2017 15:02:52 GMT

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_

Re: Formatting grand totals in datasets

art297 — Tue, 26 Dec 2017 15:24:01 GMT

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

Re: Formatting grand totals in datasets

Tom — Tue, 26 Dec 2017 15:25:26 GMT

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;

Re: Formatting grand totals in datasets

Cynthia_sas — Tue, 26 Dec 2017 15:26:11 GMT

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

Re: Formatting grand totals in datasets

stat_sas — Tue, 26 Dec 2017 16:31:30 GMT

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;

Re: Formatting grand totals in datasets

Cynthia_sas — Tue, 26 Dec 2017 23:49:44 GMT

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