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
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.