Customer Information Summarized

[altijani]

Hi Community,

I have the following customer data that has multiple records per customer (this is just a sample):

Customer_ID	Name	Status	Status_Date
1234	ABC	Open	01/02/2016
1234	ABC	Closed	06/07/2017
1234	ABC	Open	01/15/2018
3421	XYZ	Open	09/22/2014
3421	XYZ	Pending	09/06/2015
3421	XYZ	Closed	06/14/2016
3421	XYZ	Open	06/12/2017
9120	BBB	Open	02/05/2016
9120	BBB	Open	08/11/2017
9120	BBB	Closed	09/01/2017

And I need to create only one line that summarizes the needed information from the above sample data, as follows:

Customer_ID	Name	Ever_Pending_Close_0_3_Years	Ever_Pending_Closed_3_5_Years	Open_Now
1234	ABC	Yes	No	Yes
3421	XYZ	No	Yes	Yes
9120	BBB	Yes	No	No

The periods above are:

Ever_Pending_Close_0_3_Years = ever closed or pending in the last three years from today?

Ever_Pending_Closed_3_5_Years = ever closed or pending in the last FIVE years, but not in the last three years (between 3-5 years) from today?

Thanks!!

[RW9]

Do it stepwise, assign groups, then summarise based on the groups.  Just a rough idea here (post test data in the form of a datastep, not here to type that in):

data inter;
  set have;
  retain open_date;
  length group $200;
  by customer_id;
  if first.customer_id then open_date=status_date;
  if status="Open" and open_date=. then open_date=status_date;
  if status="Closed" then do;
    group=intnx('year',open_date,status_date);
    open_date=.;
  end;
  if last.customer_id and open_date ne . then group=open_now;
run;

Then transpose up distinct records of customer_id and group.

[novinosrin]

data have;
input Customer_ID	Name $	Status $	Status_Date :mmddyy10.;
format Status_Date mmddyy10.;
cards;
1234	ABC	Open	01/02/2016
1234	ABC	Closed	06/07/2017
1234	ABC	Open	01/15/2018
3421	XYZ	Open	09/22/2014
3421	XYZ	Pending	09/06/2015
3421	XYZ	Closed	06/14/2016
3421	XYZ	Open	06/12/2017
9120	BBB	Open	02/05/2016
9120	BBB	Open	08/11/2017
9120	BBB	Closed	09/01/2017
;
data want;
if 0 then set have;
Ever_Pending_Close_0_3_Years='NO   ';
Ever_Pending_Closed_3_5_Years='NO  ';
Open_Now='NO   ';
do until(last.name);
set have;
by customer_id name notsorted;
if intnx('year', today(),-2,'s')<(Status_Date)<=today() and Status in ('Closed','Pending') then Ever_Pending_Close_0_3_Years='YES';
If intnx('year', today(),-4,'s')<(Status_Date)<=intnx('year', today(),-2,'s') and Status in ('Closed','Pending') then Ever_Pending_Closed_3_5_Years='YES';
if last.name and status='Open' then Open_Now='YES';
end;
drop Status 	Status_Date;
run;

[altijani]

Thanks Novinosrin

Everything seems working except that my date format is this: DATETIME26.6, which results in not calculating the periods at all.

Any workaround?

Thanks

[novinosrin]

Do you mean your status_date is a datetime value and not a date value?

In that case,

if intnx('year', today(),-2,'s')<datepart(Status_Date)<=today() and Status in ('Closed','Pending') then Ever_Pending_Close_0_3_Years='YES';
If intnx('year', today(),-4,'s')<datepart(Status_Date)<=intnx('year', today(),-2,'s') and Status in ('Closed','Pending') then Ever_Pending_Closed_3_5_Years='YES';