Hi,
I have a dataset:
Client | Business Stage | Date and Time |
AAA | 1 | 20-Dec-18 |
AAA | 2 | 21-Dec-18 |
AAA | 3 | 24-Dec-18 |
AAA | 1 | 05-Jan-19 |
AAA | 3 | 06-Jan-19 |
BBB | 1 | 20-Dec-18 |
BBB | 2 | 21-Dec-18 |
I want to know how much time each client spent in each of the business stages. I want:
Client | Business Stage | Time Spent (Days) |
AAA | 1 | 2 |
AAA | 2 | 3 |
AAA | 3 | 12 |
BBB` | 1 | 1 |
The tricky thing is that each client can go back and fourth between stages. As you see here, Client AAA went to stage 1 twice. So client AAA spend 2 days in stage 1.
Thanks for helping.
UNTESTED CODE (and assumes everything is sorted by client and date, and the dates are actual SAS date values)
data re_arrange;
merge have have(firstobs=2 rename=(client=client2 date=date2) drop=stage);
elapsed=date2-date1;
if client2^=client then delete;
run;
proc summary data=re_arrange;
class client stage;
var elapsed;
output out=want sum=sum_elapsed;
run;
UNTESTED CODE (and assumes everything is sorted by client and date, and the dates are actual SAS date values)
data re_arrange;
merge have have(firstobs=2 rename=(client=client2 date=date2) drop=stage);
elapsed=date2-date1;
if client2^=client then delete;
run;
proc summary data=re_arrange;
class client stage;
var elapsed;
output out=want sum=sum_elapsed;
run;
A hash object approach
data have;
input Client $ Business_Stage Date:date9.;
format Date date9.;
datalines;
AAA 1 20-Dec-18
AAA 2 21-Dec-18
AAA 3 24-Dec-18
AAA 1 05-Jan-19
AAA 3 06-Jan-19
BBB 1 20-Dec-18
BBB 2 21-Dec-18
;
data _null_;
if _N_ = 1 then do;
declare hash h(ordered:'A');
h.defineKey('Client', 'Business_Stage');
h.defineData('Client', 'Business_Stage', 'time_spent');
h.defineDone();
end;
merge have have(firstobs=2 rename=(client=client2 date=date2) drop=Business_Stage) end=lr;
if client2 ne client then time_spent=0;
else time_spent=intck('day', date, date2);
if h.check() ne 0 then h.add();
else do;
_time_spent=time_spent;
rc=h.find();
time_spent=time_spent+_time_spent;
h.replace();
end;
if lr then h.output(dataset:'want(where=(time_spent ne 0))');
run;
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.