Sum by ID across rows and columns

[ModeratelyWise]

Hello all,

So I have a dataset of clients that is made up of multiple episode and the episodes are made up of multiple events.  I want to sum the total days a client spent during a particular episode (i.e. sum all the days for each event that occurred during the episode).

Client_ID	Episode_ID	Event_ID	EvntDays1	EvntDays2	EvntDays3	EvntDays4	EvntDays5
001	001	001	6	0	0	0	0
001	001	002	0	6	0	0	0
001	002	003	0	0	0	0	2
002	003	004	3	0	0	0	0
002	003	005	0	0	0	8	0
002	003	006	0	8	0	0	0
002	004	007	2	0	0	0	0
002	005	008	0	0	5	0	0

EvntDays1, EvntDays2, etc are the number of days spent in a particular event placement and there will be only one non-zero column per row.  I want the end results to look like this:

Client_ID	Episode_ID	Total_Days
001	001	12
001	002	2
002	003	19
002	004	2
002	005	5

Does that make sense?  Let me know what you think.

Best,

Marc

[ChrisNZ · Accepted solution]

Try:

data WANT 

  set HAVE;

  by Client_ID Episode_ID;

  if first.Episode_ID then Total_Days=0;

  Total_Days+sum(of EvntDays:);

  if last.Episode_ID;

run;

Untested as no data was supplied in usable form.

[ModeratelyWise]

Thanks @ChrisNZ .  This worked! So did @Ksharp's code.  I just went in sequence.  

[Ksharp]

UNTESTED CODE:

proc sql;
create table want as
select Client_ID ,Episode_ID ,
sum(EvntDays1)+sum(EvntDays2)+sum(EvntDays3)+sum(EvntDays4)+sum(EvntDays5)
as Total_Days
from have
group by Client_ID ,Episode_ID;
quit;

[maguiremq]

This is the reusable form that @ChrisNZ is talking about:

data have;
infile datalines truncover;
input Client_ID	$ Episode_ID $	Event_ID $	EvntDays1	EvntDays2	EvntDays3	EvntDays4	EvntDays5;
datalines;
001	001	001	6	0	0	0	0
001	001	002	0	6	0	0	0
001	002	003	0	0	0	0	2
002	003	004	3	0	0	0	0
002	003	005	0	0	0	8	0
002	003	006	0	8	0	0	0
002	004	007	2	0	0	0	0
002	005	008	0	0	5	0	0
;

There are probably better resources, but here is one.

Both @ChrisNZ's and @Ksharp 's solutions work.

[ModeratelyWise]

@maguiremq Thanks! I will make sure to format the data like this in the future.