Solved
Contributor
Posts: 54

# Calculating totals

Hello,

I have a dataset that looks something like this:

 Type Code Date Bankruptcy_Count 1 Sector 11 Apr-16 2 2 Sector 11 May-16 5 3 Sector 11 Jun-16 6 4 Sector 21 Apr-16 8 5 Sector 21 May-16 4 6 Sector 21 Jun-16 9 7 Sector 31 Apr-16 5 8 Sector 31 May-16 2 9 Sector 31 Jun-16 1

I want to create totals by month by inserting a new Type called "Total' with the code of 1.  I would like the end product to look something like this with Total being included in the Type heading and Bankruptcy_Count the sum of the monthly sector values 11+21+31:

 Type Code Date Bankruptcy_Count 1 Total 1 Apr-16 15 2 Total 1 May-16 11 3 Total 1 Jun-16 16 4 Sector 11 Apr-16 2 5 Sector 11 May-16 5 6 Sector 11 Jun-16 6 7 Sector 21 Apr-16 8 8 Sector 21 May-16 4 9 Sector 21 Jun-16 9 10 Sector 31 Apr-16 5 11 Sector 31 May-16 2 12 Sector 31 Jun-16 1

What is the easiest way to code this in SAS.  Do I need to create a new dataset?

Thanks

Jack

Accepted Solutions
Solution
‎09-01-2016 01:16 PM
Super User
Posts: 10,761

## Re: Calculating totals

```
Sure. But you didn't post the output yet ?

data have;
infile cards expandtabs truncover;
input obs Type : \$20.	Code	Date \$	Bankruptcy_Count;
drop obs;
cards;
1	Sector	11	16-Apr	2
2	Sector	11	16-May	5
3	Sector	11	16-Jun	6
4	Sector	21	16-Apr	8
5	Sector	21	16-May	4
6	Sector	21	16-Jun	9
7	Sector	31	16-Apr	5
8	Sector	31	16-May	2
9	Sector	31	16-Jun	1
10	Subsector	111	16-Apr	1
11	Subsector	111	16-May	3
12	Subsector	111	16-Jun	2
13	Subsector	211	16-Apr	4
14	Subsector	211	16-May	2
15	Subsector	211	16-Jun	4
16	Subsector	311	16-Apr	2
17	Subsector	311	16-May	1
18	Subsector	311	16-Jun	1
;
run;
proc sql;
create table want as
select 'Total' as type length=20,1 as code,
Date,
sum(Bankruptcy_Count) as Bankruptcy_Count
from have
where int(Code/10) between 1 and 9
group by Date
union all
select 'Total' as type length=20,2 as code,
Date,
sum(Bankruptcy_Count) as Bankruptcy_Count
from have
where int(Code/100) between 1 and 9
group by Date
union all
select * from have;
quit;

```

All Replies
Super Contributor
Posts: 271

## Re: Calculating totals

One way, I can immediately think of create temp dataset with code 1 and append to your original dataset.

``````proc sql;
create table code_1 as
select  Type,
1 as Code,
Date,
sum(Bankruptcy_count) as Bankruptcy_count
from Input
group by
Type, Code, Date;
quit;

proc append base= input data=code_1;
run;``````
Contributor
Posts: 54

## Re: Calculating totals

Thanks....looks good.

Posts: 1,147

## Re: Calculating totals

``````proc sort data=have;
by date;
run;

data want(drop=count);
do until(last.date);
set have;
by date;
retain count;
if first.date then count=Bankruptcy_Count;
else count+Bankruptcy_Count;
if last.date then do;
type='Total';
code=1;
Bankruptcy_Count=count;
output;
end;
end;
do until(last.date);
set have;
by date;
output;
end;
run;``````
Thanks,
Jag
Contributor
Posts: 54

Thank you!

Contributor
Posts: 54

## Re: Calculating totals

Thanks Jag...one question.

The code you generated from my example would be for totaling values from three, two-digit codes by month.  The totaled values go by the code '1' by month.

Let's suppose I need to expand this code to generate the same calculation for three-digit codes and four-digit codes and these need to be represented by the codes '2' and codes '3' respectively  How do you modify your code to allow this loop?

Thanks

Jack

Super User
Posts: 6,751

## Re: Calculating totals

[ Edited ]

If you are just looking for a report, you should be able to get it without creating a new data set.  (All of this is untested code so it may require some debugging.)  Try PROC TABULATE:

proc tabulate data=have;

class type code date;

var bankruptcy_count;

tables (all  all  date) (type * code * date), Bankruptcy_count * sum=' ';

run;

There are many ways to make the table fancier, but that should be a good start.

Super User
Posts: 10,761

## Re: Calculating totals

```
data have;
infile cards expandtabs truncover;
input obs Type \$	Code	Date : monyy7.	Bankruptcy_Count;
format date monyy7.;
drop obs;
cards;
1	Sector	11	Apr-16	2
2	Sector	11	May-16	5
3	Sector	11	Jun-16	6
4	Sector	21	Apr-16	8
5	Sector	21	May-16	4
6	Sector	21	Jun-16	9
7	Sector	31	Apr-16	5
8	Sector	31	May-16	2
9	Sector	31	Jun-16	1
;
run;
proc sql;
create table want as
select 'Total' as type length=20,1 as code,
Date,
sum(Bankruptcy_Count) as Bankruptcy_Count
from have
group by Date
union all
select * from have;

quit;

```
Contributor
Posts: 54

## Re: Calculating totals

Suppose I wanted to create the same totals for the 2-digit sector but also the 3-digit sector as well:

 1 Sector 11 16-Apr 2 2 Sector 11 16-May 5 3 Sector 11 16-Jun 6 4 Sector 21 16-Apr 8 5 Sector 21 16-May 4 6 Sector 21 16-Jun 9 7 Sector 31 16-Apr 5 8 Sector 31 16-May 2 9 Sector 31 16-Jun 1 10 Subsector 111 16-Apr 1 11 Subsector 111 16-May 3 12 Subsector 111 16-Jun 2 13 Subsector 211 16-Apr 4 14 Subsector 211 16-May 2 15 Subsector 211 16-Jun 4 16 Subsector 311 16-Apr 2 17 Subsector 311 16-May 1 18 Subsector 311 16-Jun 1

So the code would be modified to incorporate a loop so that I generate the monthly totals for each of the 2-digit (represented previously by code '1') and 3-digit codes (say represented by code '2')?

Thanks...Jack

Solution
‎09-01-2016 01:16 PM
Super User
Posts: 10,761

## Re: Calculating totals

```
Sure. But you didn't post the output yet ?

data have;
infile cards expandtabs truncover;
input obs Type : \$20.	Code	Date \$	Bankruptcy_Count;
drop obs;
cards;
1	Sector	11	16-Apr	2
2	Sector	11	16-May	5
3	Sector	11	16-Jun	6
4	Sector	21	16-Apr	8
5	Sector	21	16-May	4
6	Sector	21	16-Jun	9
7	Sector	31	16-Apr	5
8	Sector	31	16-May	2
9	Sector	31	16-Jun	1
10	Subsector	111	16-Apr	1
11	Subsector	111	16-May	3
12	Subsector	111	16-Jun	2
13	Subsector	211	16-Apr	4
14	Subsector	211	16-May	2
15	Subsector	211	16-Jun	4
16	Subsector	311	16-Apr	2
17	Subsector	311	16-May	1
18	Subsector	311	16-Jun	1
;
run;
proc sql;
create table want as
select 'Total' as type length=20,1 as code,
Date,
sum(Bankruptcy_Count) as Bankruptcy_Count
from have
where int(Code/10) between 1 and 9
group by Date
union all
select 'Total' as type length=20,2 as code,
Date,
sum(Bankruptcy_Count) as Bankruptcy_Count
from have
where int(Code/100) between 1 and 9
group by Date
union all
select * from have;
quit;

```
Contributor
Posts: 54