## How to create sub-totals ?

Frequent Contributor
Posts: 122

# How to create sub-totals ?

Hi,

I have a  dataset like below:

 Race Group Location Date Count White All LA Aug2016 1 White All ANA Aug2016 2 Black All LA Aug2016 2 Black All ANA Aug2016 3

I want the dataset to be like this:

 Race Group Location Date Count White All LA Aug2016 1 White All ANA Aug2016 2 White All Region Aug2016 3 Black All LA Aug2016 2 Black All ANA Aug2016 3 Black All Region Aug2016 5

Super User
Posts: 6,935

## Re: How to create sub-totals ?

So what constitutes a group where you want subtotals?  Just RACE?  RACE + one of the other variables?  Are GROUP and DATE always constant for a single value of RACE?  What is the length of the existing variable LOCATION?

Frequent Contributor
Posts: 122

## Re: How to create sub-totals ?

[ Edited ]

Group variables should be RACE+GROUP+DATE. GROUP and DATE  are always constant for a single value of RACE.

Length of LOCATION IS 10.

Super User
Posts: 6,935

## Re: How to create sub-totals ?

[ Edited ]

OK, let's try it this way then:

data want;

set have;

by race notsorted;

if first.race then totcount = count;

else totcount + count;

output;

if last.race;

count = totcount;

location = 'Region';

output;

drop totcount;

run;

Frequent Contributor
Posts: 122

## Re: How to create sub-totals ?

Thanks.

Just curious ..if the GROUP  and DATE are not constant,then what will be the code change?

Super User
Posts: 6,935

## Re: How to create sub-totals ?

First you have to decide what the result would be.  Then we can talk about code changes.  If the other variables change, what values belong on the "Region" observations?  That's one of the choices you have to make.

Frequent Contributor
Posts: 122

## Re: How to create sub-totals ?

Sure.

Here is my new have:

 Race Group Location Date Count White All LA Aug-16 1 White All ANA Aug-16 2 Black All LA Aug-16 2 Black All ANA Aug-16 3 Black 65P ANA Aug-16 1

Desired output:

 Race Group Location Date Count White All LA Aug-16 1 White All ANA Aug-16 2 White All Region Aug-16 3 Black All LA Aug-16 2 Black All ANA Aug-16 3 Black All Region Aug-16 5 Black 65P ANA Aug-16 1 Black 65P Region 1-Aug 1

Super User
Posts: 6,935

## Re: How to create sub-totals ?

OK, here's a program to match:

data want;

set have;

by race group notsorted;

if first.group then totcount = count;

else totcount + count;

output;

if last.group;

count = totcount;

location = 'Region';

output;

drop totcount;

run;

For this to work, your data has to be grouped:  each RACE/GROUP combination should form a set of adjacent observations.

Frequent Contributor
Posts: 122

Thank you!

Valued Guide
Posts: 765

## Re: How to create sub-totals ?

[ Edited ]

Hi, how about PROC SUMMARY. Only issue is that the value of the variable LOCATION is missing rather than "REGION" (you can fix that in a subsequent data step). Since there is no ascending or descending order of the location variable in your output, you'd have to play games with the values to get the order you show. (e.g. leading spaces)  ...

data x;
input race :\$5. group :\$3. location :\$6. date :monyy. count;
format date monyy.;
datalines;
White All LA Aug-16 1
White All ANA Aug-16 2
Black All LA Aug-16 2
Black All ANA Aug-16 3
Black 65P ANA Aug-16 1
;

proc summary data=x;
class race group location date;
var count;
output out=y (drop=_: )sum=;
types race*group*location*date race*group*date;
run;

data y;
set y;
location = ifc(missing(location),'Region',location);
run;

``````DATA SET: y

race     group    location    date     count

Black     65P      Region     AUG16      1
Black     All      Region     AUG16      5
White     All      Region     AUG16      3
Black     65P      ANA        AUG16      1
Black     All      ANA        AUG16      3
Black     All      LA         AUG16      2
White     All      ANA        AUG16      2
White     All      LA         AUG16      1
``````

Super User
Posts: 13,942

renjithr wrote:

Hi,