DATA Step, Macro, Functions and more

Calculating totals

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

Calculating totals

Hello,

 

I have a dataset that looks something like this:

 

 TypeCodeDateBankruptcy_Count
1Sector11Apr-162
2Sector11May-165
3Sector11Jun-166
4Sector21Apr-168
5Sector21May-164
6Sector21Jun-169
7Sector31Apr-165
8Sector31May-162
9Sector31Jun-161

 

 

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:

 

 TypeCodeDateBankruptcy_Count
1Total1Apr-1615
2Total1May-1611
3Total1Jun-1616
4Sector11Apr-162
5Sector11May-165
6Sector11Jun-166
7Sector21Apr-168
8Sector21May-164
9Sector21Jun-169
10Sector31Apr-165
11Sector31May-162
12Sector31Jun-161

 

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: 9,681

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;

View solution in original post


All Replies
Regular Contributor
Posts: 241

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: 52

Re: Calculating totals

Thanks....looks good.

Trusted Advisor
Posts: 1,128

Re: Calculating totals

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;
Thanks,
Jag
Contributor
Posts: 52

Re: Calculating totals

Thank you!

Contributor
Posts: 52

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

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: 9,681

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: 52

Re: Calculating totals

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:

 

1Sector1116-Apr2
2Sector1116-May5
3Sector1116-Jun6
4Sector2116-Apr8
5Sector2116-May4
6Sector2116-Jun9
7Sector3116-Apr5
8Sector3116-May2
9Sector3116-Jun1
10Subsector11116-Apr1
11Subsector11116-May3
12Subsector11116-Jun2
13Subsector21116-Apr4
14Subsector21116-May2
15Subsector21116-Jun4
16Subsector31116-Apr2
17Subsector31116-May1
18Subsector31116-Jun1

 

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: 9,681

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: 52

Re: Calculating totals

Very helpful, thank you.....

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 548 views
  • 4 likes
  • 5 in conversation