topic Re: Sum by ID across rows and columns in SAS Programming

Sum by ID across rows and columns

ModeratelyWise — Tue, 17 Aug 2021 23:38:42 GMT

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

Re: Sum by ID across rows and columns Client_ID Episode_ID

ChrisNZ — Wed, 18 Aug 2021 01:43:20 GMT

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.

Re: Sum by ID across rows and columns

Ksharp — Wed, 18 Aug 2021 12:36:31 GMT

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;

Re: Sum by ID across rows and columns

maguiremq — Wed, 18 Aug 2021 12:50:03 GMT

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.

Re: Sum by ID across rows and columns Client_ID Episode_ID

ModeratelyWise — Mon, 23 Aug 2021 22:41:48 GMT

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

Re: Sum by ID across rows and columns

ModeratelyWise — Mon, 23 Aug 2021 22:42:41 GMT

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