calculations vertically

[kz134]

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. 

[PaigeMiller · Accepted solution]

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;

[kz134]

this worked. thank you

[PeterClemmensen]

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;