Calculating Cumulative days with Overlap

[serena13lee]

Thank you so much for your quick response. Your explanation was super clear and I have a better idea of what the code is doing. You are right that my days = end_date - start_date +1. Apologies for the confusion. Since I am struggling a bit to add bits to the code I have created a new query so I don't ask too many follow up questions and waste your time (https://communities.sas.com/t5/SAS-Programming/Flagging-Overlapping-Dates-that-contribute-to-gt-15-D...)  But thank you again. You've been incredibly helpful! 

[koyelghosh]

@serena13lee Very late I realised that may be you are interested in how many days a patient skipped the medicines and how many days a patient took medicines (irrespective of which medicine was taken or skipped). So I thought may be you wanted a table like below.

Output table

So after a bit of workaround and a bit of time investment, I got the code as below. I am sure there is an easier way to accomplish the same task. 

DATA Want ;
	SET have;
	BY ID medication;
	RETAIN LowerBoundary 0;
	RETAIN UpperBoundary 0;
	RETAIN GapDays 0;
	IF FIRST.ID AND LAST.ID  THEN DO;
		LowerBoundary = MIN(start_date, end_date);
		UpperBoundary = MAX(start_date, end_date);
		Cummulative = INTCK('day',LowerBoundary,UpperBoundary)+1;
		Gapdays = 0;
		OUTPUT;
		LowerBoundary = 0;
		UpperBoundary = 0;
	END;
	ELSE IF FIRST.ID AND NOT LAST.ID THEN DO;
		LowerBoundary = MIN(start_date, end_date);
		UpperBoundary = MAX(start_date, end_date);
		Cummulative = .;
		Gapdays = 0;
		OUTPUT;		
	END;
	ELSE IF NOT FIRST.ID AND LAST.ID THEN DO;
		IF start_date > UpperBoundary THEN GapDays + INTCK('day',UpperBoundary,start_date) - 1;
		IF MIN(start_date, end_date) <= LowerBoundary THEN LowerBoundary = MIN(start_date, end_date);
		IF MAX(start_date, end_date) >= UpperBoundary THEN UpperBoundary = MAX(start_date, end_date);
		Cummulative = INTCK('day',LowerBoundary,UpperBoundary)+1 - GapDays;
		OUTPUT;
		LowerBoundary = 0;
		UpperBoundary = 0;
	END;
	ELSE DO;
		IF start_date > UpperBoundary THEN GapDays + INTCK('day',start_date,UpperBoundary) - 1;
		IF MIN(start_date, end_date) <= LowerBoundary THEN LowerBoundary = MIN(start_date, end_date);
		IF MAX(start_date, end_date) >= UpperBoundary THEN UpperBoundary = MAX(start_date, end_date);
		Cummulative = .;
		OUTPUT;
	END;	
RUN;

PROC REPORT DATA=Want;
	COLUMNS ID GapDays Cummulative;
	DEFINE ID / GROUP "Patient ID" CENTER;
	DEFINE GapDays / ANALYSIS SUM "Number of Days medicine skipped" CENTER;
	DEFINE Cummulative / ANALYSIS SUM "Total Number of days medicine taken" CENTER;
RUN;

Please let me know, when you can, if this was what you wanted.

[serena13lee]

Thank you for your solution! It is a very clean method of getting the outcome and I am fitting it to my data to see how it is done in-depthly. Really appreciate your taking the time to look into it as I was struggling to find a way myself. 

[hashman]

@serena13lee:

You may have noticed that the people who have responded to your inquiry have had to guess what you output is supposed to look like. They wouldn't have had to had you provided a WANT data set to look at. Please afford this courtesy to those kind enough to take their time to help you out and don't assume that your verbal explanation of the result you want to achieve is sufficiently lucid.

Kind regards

Paul D.  

[serena13lee]

Thank you for your suggestion. I will keep that in mind. Apologies for the confusion.