DATA Step, Macro, Functions and more

How to create sub-totals ?

Reply
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

 

Please share your thoughts.

Super User
Posts: 5,082

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: 5,082

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: 5,082

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

 

Please let me know.

Super User
Posts: 5,082

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

Re: How to create sub-totals ?

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: 10,500

Re: How to create sub-totals ?


renjithr wrote:

Hi,

 

Please share your thoughts.


Don't put subtotals into data generally.

I would do that as part of a final report using a report procedure such as Report or Tabulate.

Ask a Question
Discussion stats
  • 10 replies
  • 169 views
  • 1 like
  • 4 in conversation