topic Re: How to create sub-totals ? in SAS Programming

How to create sub-totals ?

renjithr — Tue, 25 Jul 2017 20:58:21 GMT

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.

Re: How to create sub-totals ?

Astounding — Tue, 25 Jul 2017 21:07:55 GMT

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?

Re: How to create sub-totals ?

renjithr — Tue, 25 Jul 2017 21:13:59 GMT

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

Length of LOCATION IS 10.

Re: How to create sub-totals ?

Astounding — Tue, 25 Jul 2017 21:16:37 GMT

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;

Re: How to create sub-totals ?

ballardw — Tue, 25 Jul 2017 21:43:03 GMT

@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.

Re: How to create sub-totals ?

renjithr — Tue, 25 Jul 2017 22:09:51 GMT

Thanks.

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

Re: How to create sub-totals ?

Astounding — Wed, 26 Jul 2017 03:30:01 GMT

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.

Re: How to create sub-totals ?

renjithr — Wed, 26 Jul 2017 22:43:51 GMT

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.

Re: How to create sub-totals ?

Astounding — Wed, 26 Jul 2017 23:37:44 GMT

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.

Re: How to create sub-totals ?

renjithr — Thu, 27 Jul 2017 19:32:42 GMT

Thank you!

Re: How to create sub-totals ?

MikeZdeb — Thu, 27 Jul 2017 23:08:37 GMT

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