Calculating totals

[Jack1]

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

[RahulG]

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;

[Jack1]

Thanks....looks good.

[Jagadishkatam]

Please try do until

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;

[Jack1]

Thank you!

[Jack1]

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

[Astounding]

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.

[Ksharp]

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;

[Jack1]

Thanks....that is helpful.

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

[Ksharp · Accepted solution]

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;

[Jack1]

Very helpful, thank you.....