topic Re: calculations vertically in SAS Programming

calculations vertically

kz134 — Mon, 07 Jan 2019 19:27:11 GMT

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.

Re: calculations vertically

PaigeMiller — Mon, 07 Jan 2019 19:39:04 GMT

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;

Re: calculations vertically

kz134 — Mon, 07 Jan 2019 19:57:47 GMT

this worked. thank you

Re: calculations vertically

PeterClemmensen — Mon, 07 Jan 2019 20:22:09 GMT

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;