Calculating the number of interactions with a client until their problem(s) has been resolved

[Data_Analyst]

I have a date set similar to the one below. Clients come with issue(s) that need to be addressed. They periodically come back to check on the issue(s) resolution status. All issues are being followed until a resolution is found. The date a client reports a new issue is captured as  "First Visit". Ideally, when the client returns, all issues would be resolved, however this is s not always the case. Each unresolved issue is discussed at every visit, however the client may come many times  before we have a solution. Once the issue is resolved the date is captured in the variable "Resolution". 

Client	Issue	First Visit	Resolution	Number of Visits
Deb	Issue1	25-Apr-22	07-Jun-22	1
Deb	Issue2	25-Apr-22	07-Jun-22	1
Deb	Issue3	25-Apr-22	07-Jun-22	1
Deb	Issue4	07-Jul-22	15-Aug-22	2
Deb	Issue5	07-Jul-22	20-Oct-22	3
Deb	Issue6	15-Aug-22	20-Oct-22
Deb	Issue7	15-Aug-22
Deb	Issue8	20-Oct-22
Deb	Issue9	20-Oct-22
Jack	Issue1	14-Mar-22	22-Sep-22
Jack	Issue2	14-Mar-22	06-Jul-22
Jack	Issue3	14-Mar-22	06-Jul-22
Jack	Issue4	14-Mar-22
Jack	Issue5	14-Mar-22	06-Jul-22
Jack	Issue6	05-Jul-22	22-Sep-22
Jack	Issue7	05-Jul-22	22-Sep-22
Jack	Issue8	05-Jul-22	22-Sep-22
Jack	Issue9	05-Jul-22	22-Sep-22
Jack	Issue10	05-Jul-22	22-Sep-22
Jack	Issue11	21-Sep-22

I need to calculate how many times (number of visits)  each client came back to troubleshoot to the same issue:

- Resolved  issues: the number of visits.

- Unresolved Issues: the number of visits between the last visit on record and the first visit.

My actual data set includes hundreds of clients and thousands of issues.

Any help would be appreciated.

[Reeza]

Please show what the input data looks like. This looks like the output data, and I would expect to say a date of visit for example in the input data.

[Data_Analyst]

Hi, this is the input data. I have an Excel spreadsheet which is the result from a database query. I am planning to import the spreadsheet in sas and calculate the number of visits.  I hope this helps.

[Data_Analyst]

This is a tricky problem to solve because it requires manipulation of individual variables/observations within the data set as oppose to working with the entire set.

I am happy to report that managed to calculate how many times each customer came back before their issues were resolved.

I followed the approach described in this publication:

https://support.sas.com/resources/papers/proceedings/proceedings/sugi26/p093-26.pdf

I took some time to make it work but worked!

I am sharing, just in case somebody else has a similar problem.

Cheers!