BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jack1
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

10 REPLIES 10
RahulG
Barite | Level 11

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
Obsidian | Level 7

Thanks....looks good.

Jagadishkatam
Amethyst | Level 16

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
Jack1
Obsidian | Level 7

Thank you!

Jack1
Obsidian | Level 7

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
PROC Star

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
Super User

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
Obsidian | Level 7

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

Ksharp
Super User

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
Obsidian | Level 7

Very helpful, thank you.....

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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