topic Re: How to create a variable to represent the months of overlapping drug use in SAS Programming

How to create a variable to represent the months of overlapping drug use

CynthiaWei — Mon, 21 Sep 2020 00:59:50 GMT

Hi SAS Pros,

I am having this dataset. My goal is to know the dates that a patient used drug B while using A, which is the overlapping use, among all the prescriptions from 1/1/2017 to 4/30/2018.

For the overlapping use dates, I don't need to know what exactly they are.
What I need to generate is the months of those dates with overlapping use of drug A and B, which are from 2017-01 to 2018-04, any overlapping use happened after 2018-04 won't be considered as an overlap.
For example, the months with overlapping use for patient 1 are 2017-01, 2017-02, 2017-04, 2017-05, 2017-06, 2018-01, 2018-02, 2018-03, and 2018-04
Right now, I have two definition of overlapping use because the clinical recommendation of use for drug A and B.
The definition 1 of having drug B while using A (overlapping use) is that a patient used them on the same day (e.g. just like data showed below in the first and second prescriptions for patient 1).
What if the definition (definition 2) of overlapping use is 3 days apart, that is, the start_taking_date is <= 3 days after last_taking_date of previous prescription of different drug.

ID	No_of_Prescription	Start_taking_Date	Day_Supply	last_taking_date	drug
1	1	1/2/2017	3	1/4/2017	A
1	2	1/4/2017	30	2/2/2017	B
1	3	1/4/2017	30	2/2/2017	A
1	4	1/4/2017	30	2/2/2017	A
1	5	4/7/2017	10	4/16/2017	B
1	6	4/7/2017	10	4/16/2017	A
1	7	4/14/2017	30	5/13/2017	A
1	8	4/17/2017	30	5/16/2017	A
1	9	5/9/2017	30	6/7/2017	B
1	10	5/16/2017	30	6/14/2017	A
1	11	1/17/2018	60	3/17/2018	B
1	12	1/23/2018	30	2/21/2018	A
1	13	2/13/2018	30	3/14/2018	A
1	14	2/15/2018	30	3/16/2018	B
1	15	3/14/2018	30	4/12/2018	A
1	16	3/14/2018	30	4/12/2018	A
1	17	4/1/2018	30	4/30/2018	B
2	3	2/9/2017	30	3/10/2017	B
2	4	2/9/2017	30	3/10/2017	B
2	5	2/16/2017	30	3/17/2017	A
2	6	3/13/2017	30	4/11/2017	B
2	7	3/13/2017	30	4/11/2017	B
2	8	3/17/2017	30	4/15/2017	A
2	9	4/10/2017	30	5/9/2017	B
2	10	4/15/2017	30	5/14/2017	A
2	11	5/14/2017	352	4/30/2018	B
2	12	5/14/2017	30	6/12/2017	A

I know this project is a little complicated. I appreciate any help with it! (I have asked a similar question before, but this one is different, if you happened to see my previous post, please ignore the previous ones.)

Best regards,

Re: How to create a variable to represent the months of overlapping drug use

jimbarbour — Mon, 21 Sep 2020 06:51:24 GMT

Hi, @CynthiaWei,

I'm not sure if I completely understood all of your requirements, but I have written a program that you should be able to adjust to fit as your needs dictate.

The program sets the "Both" variable to "1" for any month where both drug A and drug B were taken any time during the month. If either drug A and drug B were not taken any time during the month, the "Both" variable is set to "0."

Details of the program:

A start of period and end of period macro variable are set. I used 01JAN2017 as the start of the period of interest and 30APR2018 as the end of the period of interest. Throughout the program, I used the first day of the month for whatever date was under consideration and the last day of the month for any end dates.
A simple DATA step reads the input. I just cut and paste the data from your original post into this DATA step.
A second DATA step builds two hash tables, one for drug A and one for drug B. For each ID, every month in the data is evaluated from start to end, and an entry with a key of ID and Drug_Date is written to the respective hash table if a particular drug was taken any time during the month. Basically each has table contains a record for each ID for each month a particular drug was taken.
In the second data step, after the hash tables are loaded, each month in the period of interest is examined for each ID. If an entry is found in the drug A and in the drug B hash table for an ID for a particular month, then the "Both" variable is set to "1" otherwise the variable is set to "0".

The code is below, and, following the code, are the results.

Jim

%LET	Start_Of_Period		=	'01JAN2017'd;
%LET	End_Of_Period		=	'30APR2018'd;

DATA	Rx_Dates;
	INFILE	Datalines4	
		DLM					=	'09'x
		FIRSTOBS			=	2
		DSD
		;

	INPUT
		ID					$
		No_of_Prescription	$
		Start_Taking_Date	:	ANYDTDTE10.
		Day_Supply			$
		Last_Taking_Date	:	ANYDTDTE10.
		Drug				$
		;

	FORMAT	Start_Taking_Date	MMDDYYS10.;
	FORMAT	Last_Taking_Date	MMDDYYS10.;

DATALINES4;
ID	No_of_Prescription	Start_Taking_Date	Day_Supply	Last_Taking_Date	Drug
1	1	1/2/2017	3	1/4/2017	A
1	2	1/4/2017	30	2/2/2017	B
1	3	1/4/2017	30	2/2/2017	A
1	4	1/4/2017	30	2/2/2017	A
1	5	4/7/2017	10	4/16/2017	B
1	6	4/7/2017	10	4/16/2017	A
1	7	4/14/2017	30	5/13/2017	A
1	8	4/17/2017	30	5/16/2017	A
1	9	5/9/2017	30	6/7/2017	B
1	10	5/16/2017	30	6/14/2017	A
1	11	1/17/2018	60	3/17/2018	B
1	12	1/23/2018	30	2/21/2018	A
1	13	2/13/2018	30	3/14/2018	A
1	14	2/15/2018	30	3/16/2018	B
1	15	3/14/2018	30	4/12/2018	A
1	16	3/14/2018	30	4/12/2018	A
1	17	4/1/2018	30	4/30/2018	B
2	3	2/9/2017	30	3/10/2017	B
2	4	2/9/2017	30	3/10/2017	B
2	5	2/16/2017	30	3/17/2017	A
2	6	3/13/2017	30	4/11/2017	B
2	7	3/13/2017	30	4/11/2017	B
2	8	3/17/2017	30	4/15/2017	A
2	9	4/10/2017	30	5/9/2017	B
2	10	4/15/2017	30	5/14/2017	A
2	11	5/14/2017	352	4/30/2018	B
2	12	5/14/2017	30	6/12/2017	A
;;;;
RUN;

**------------------------------------------------------------------------------**;

DATA	Rx_Period_Evaluated;
	KEEP	ID	Drug_Date	Both;
	LENGTH	ID						$8;
	LENGTH	Drug_Date				8.;
	FORMAT	Drug_Date				MMDDYYS10.;
	LENGTH	End_of_Taking			8.;
	FORMAT	End_of_Taking			MMDDYYS10.;
	LENGTH	Start_of_Period			8.;
	FORMAT	Start_of_Period			MMDDYYS10.;
	LENGTH	End_of_Period			8.;
	FORMAT	End_of_Period			MMDDYYS10.;
	LENGTH	Work_ID					8.;
	RETAIN	Max_ID					0;

	IF	_N_							=	1							THEN
		DO;
			CALL	MISSING(Drug_Date);
			DECLARE	HASH	Hsh_A_Dates();
			Hsh_A_Dates.DefineKey('ID', 'Drug_Date');
			Hsh_A_Dates.DefineDone();
			CALL	MISSING(Drug_Date);
			DECLARE	HASH	Hsh_B_Dates();
			Hsh_B_Dates.DefineKey('ID', 'Drug_Date');
			Hsh_B_Dates.DefineDone();
		END;

	**	Load drug A hash table.		**;
	DO	WHILE(NOT	End_Of_A);
		SET	Rx_Dates	
			END						=	End_Of_A;
			WHERE	Drug			=	'A';
		IF	INPUT(ID, 3.)			>	Max_ID	THEN
			Max_ID					=	INPUT(ID, 3.);
		Drug_Date					=	INTNX('MONTH', Start_Taking_Date, 0);
		End_of_Taking				=	INTNX('MONTH', Last_Taking_Date, 1) - 1;
		DO	WHILE(Drug_Date			<=	End_of_Taking);
			_RC_A					=	Hsh_A_Dates.ADD();
			Drug_Date				=	INTNX('MONTH', Drug_Date, 1);
		END;
	END;

	**	Load drug B hash table.		**;
	DO	WHILE(NOT	End_Of_B);
		SET	Rx_Dates	
			END						=	End_Of_B;
			WHERE	Drug			=	'B';
		IF	INPUT(ID, 3.)			>	Max_ID	THEN
			Max_ID					=	INPUT(ID, 3.);
		Drug_Date					=	INTNX('MONTH', Start_Taking_Date, 0);
		End_of_Taking				=	INTNX('MONTH', Last_Taking_Date, 1) - 1;
		DO	WHILE(Drug_Date			<=	End_of_Taking);
			_RC_B					=	Hsh_B_Dates.ADD();
			Drug_Date				=	INTNX('MONTH', Drug_Date, 1);
		END;
	END;

	Start_Of_Period					=	INTNX('MONTH', &Start_Of_Period, 0);
	End_Of_Period					=	INTNX('MONTH', &End_Of_Period, 1) - 1;

	DO	Work_ID						=	1	TO	Max_ID;
		ID							=	STRIP(PUT(Work_ID, 3.));
		Drug_Date					=	Start_Of_Period;
		Iteration					=	0;
		DO	WHILE(Drug_Date			<=	End_Of_Period);
			Iteration				+	1;
			IF	Hsh_A_Dates.FIND()	=	0	AND
				Hsh_B_Dates.FIND()	=	0	THEN
				DO;
					Both			=	1;
				END;
			ELSE
				DO;
					Both			=	0;
				END;
			OUTPUT;
			Drug_Date				=	INTNX('MONTH', Drug_Date, 1);
		END;
	END;

	STOP;
RUN;

**------------------------------------------------------------------------------**;

Results:

Re: How to create a variable to represent the months of overlapping drug use

Shmuel — Mon, 21 Sep 2020 06:37:31 GMT

You have not show the desired output format;

I have run next code:

data have;
  infile cards dlm='09'x;
  informat Start_taking_Date last_taking_date mmddyy. ;
  input ID
        No_of_Prescription
        Start_taking_Date	
		Day_Supply	
		last_taking_date	
		drug $;
cards;
1	1	1/2/2017	3	1/4/2017	A
1	2	1/4/2017	30	2/2/2017	B
1	3	1/4/2017	30	2/2/2017	A
1	4	1/4/2017	30	2/2/2017	A
1	5	4/7/2017	10	4/16/2017	B
1	6	4/7/2017	10	4/16/2017	A
1	7	4/14/2017	30	5/13/2017	A
1	8	4/17/2017	30	5/16/2017	A
1	9	5/9/2017	30	6/7/2017	B
1	10	5/16/2017	30	6/14/2017	A
1	11	1/17/2018	60	3/17/2018	B
1	12	1/23/2018	30	2/21/2018	A
1	13	2/13/2018	30	3/14/2018	A
1	14	2/15/2018	30	3/16/2018	B
1	15	3/14/2018	30	4/12/2018	A
1	16	3/14/2018	30	4/12/2018	A
1	17	4/1/2018	30	4/30/2018	B
2	3	2/9/2017	30	3/10/2017	B
2	4	2/9/2017	30	3/10/2017	B
2	5	2/16/2017	30	3/17/2017	A
2	6	3/13/2017	30	4/11/2017	B
2	7	3/13/2017	30	4/11/2017	B
2	8	3/17/2017	30	4/15/2017	A
2	9	4/10/2017	30	5/9/2017	B
2	10	4/15/2017	30	5/14/2017	A
2	11	5/14/2017	352	4/30/2018	B
2	12	5/14/2017	30	6/12/2017	A
; run;

data have_A have_B;
 drop Start_taking_Date last_taking_date;
 set have;
     do day = Start_taking_Date to last_taking_date; end;
     if drug = 'A' then output have_A;
     else output have_B;
run;

proc sort data=have_A; by id day; run;
proc sort data=have_B; by id day; run;

data want;
merge have_A (in=inA rename=(day_supply=daysupA drug=drugA))
      have_B (in=inB rename=(day_supply=daysupB drug=drugB));
   by id day;        
   if inA and inB;
   drugs = catx('+',drugA,drugB);
   drop drugA drugB;
   format day date9.;
run;

and got next results:

Re: How to create a variable to represent the months of overlapping drug use

s_lassen — Mon, 21 Sep 2020 07:50:37 GMT

I think you can do it something like this. First, create a table of overlaps:

proc sql;
  create table overlap as select distinct
    A.ID,
    max(A.Start_taking_Date,B.Start_taking_Date) as start format=date9.,
    min(A.last_taking_date,B.last_taking_date) as end format=date9.
    from
      have A join
      have B on A.ID=B.ID
    where A.drug='A'
      and B.drug='B'
    having start<=end
    order by 1,2,3
    ;
quit;

Then use a data step to get the wanted values (the LAST_DATE macro variable is because you do not want anything after 2018-04):

%let last_date='01may2018'd;
data want;
  set overlap;
  by ID;
  if first.ID or month<intnx('month',start,0) then month=intnx('month',start,0);
  do while(month<=min(intnx('month',end,0),&last_date));
    output;
    month=intnx('month',month,1);
    end;
  retain month;
  keep id month;
  format month yymmd7.;
run;

If you need the second definition of overlaps, it can be done like this. First, change the HAVING clause in the SQL:

proc sql;
  create table overlap as select distinct
    A.ID,
    max(A.Start_taking_Date,B.Start_taking_Date) as start format=date9.,
    min(A.last_taking_date,B.last_taking_date) as end format=date9.
    from
      have A join
      have B on A.ID=B.ID
    where A.drug='A'
      and B.drug='B'
    having start<=end+3
    order by 1,2,3
    ;
quit;

And then the data step, correct if START is after END:

%let last_date='01may2018'd;
data want;
  set overlap;
  by ID;
  if start>end then
    start=start-3;
  if first.ID or month<intnx('month',start,0) then month=intnx('month',start,0);
  do while(month<=min(intnx('month',end,0),&last_date));
    output;
    month=intnx('month',month,1);
    end;
  retain month;
  keep id month;
  format month yymmd7.;
run;

Re: How to create a variable to represent the months of overlapping drug use

PGStats — Mon, 21 Sep 2020 18:58:58 GMT

Using an array :

data have;
input ID No_of_Prescription Start_Date :mmddyy10. Day_Supply last_date :mmddyy10. drug $;
datalines;
1     1   1/2/2017    3   1/4/2017    A
1     2   1/4/2017    30  2/2/2017    B
1     3   1/4/2017    30  2/2/2017    A
1     4   1/4/2017    30  2/2/2017    A
1     5   4/7/2017    10  4/16/2017   B
1     6   4/7/2017    10  4/16/2017   A
1     7   4/14/2017   30  5/13/2017   A
1     8   4/17/2017   30  5/16/2017   A
1     9   5/9/2017    30  6/7/2017    B
1     10  5/16/2017   30  6/14/2017   A
1     11  1/17/2018   60  3/17/2018   B
1     12  1/23/2018   30  2/21/2018   A
1     13  2/13/2018   30  3/14/2018   A
1     14  2/15/2018   30  3/16/2018   B
1     15  3/14/2018   30  4/12/2018   A
1     16  3/14/2018   30  4/12/2018   A
1     17  4/1/2018    30  4/30/2018   B
2     3   2/9/2017    30  3/10/2017   B
2     4   2/9/2017    30  3/10/2017   B
2     5   2/16/2017   30  3/17/2017   A
2     6   3/13/2017   30  4/11/2017   B
2     7   3/13/2017   30  4/11/2017   B
2     8   3/17/2017   30  4/15/2017   A
2     9   4/10/2017   30  5/9/2017    B
2     10  4/15/2017   30  5/14/2017   A
2     11  5/14/2017   352     4/30/2018   B
2     12  5/14/2017   30  6/12/2017   A
;

proc sort data=have; by id drug start_date; run;

proc sql;
select intnx("day", min(start_date), -3), max(last_date) 
into :arrayStart, :arrayLast
from have;
quit;

data overlaps;
array d {&arrayStart:&arrayLast} $1 _temporary_;
do until(last.id);
    set have; by id;
    do dt = intnx("day", start_date, -3) to last_date;
        if missing(d{dt}) then d{dt} = drug;
        else if d{dt} ne drug then output;
        end;
    end;
format dt yymmdd10.;
keep id dt;
run;
        
proc sql;
select unique 
    id,
    intnx("month", dt, 0) as overlapMonth format=yymmd7.
from overlaps;
quit;

Re: How to create a variable to represent the months of overlapping drug use

CynthiaWei — Fri, 25 Sep 2020 01:20:38 GMT

Hi Jim,

Thank you so much for your code. The result is exactly what I want to create.

I have tried your code and SAS reads the correct number of observation where drug='A' and drug='B', but has 0 observation for the final dataset. Following is what log provided, just so you know that I change dataset to a simpler one to have a easy way to find whether the code is working. I am attaching this dataset here below. Is it because I changed character/numeric variables?

SAS Log:

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
631:19 632:47 646:19 647:47
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
660:41
NOTE: There were 6 observations read from the data set WORK.TEST.
WHERE Drug='A';
NOTE: There were 7 observations read from the data set WORK.TEST.
WHERE Drug='B';
NOTE: The data set WORK.RX_PERIOD_EVALUATED has 0 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds

Thank you so much!

Best regards,

Re: How to create a variable to represent the months of overlapping drug use

CynthiaWei — Fri, 25 Sep 2020 01:31:15 GMT

Hi,

Thank you so much for your code!

What my desired output is just exactly like Jim's result table.

I tried your code and didn't get your result table. What I got is that I can create have_A and have_B but failed to create dataset-want. It seems SAS has the same issue when I tried Jim's code. This is what SAS log provided (I am using a simpler dataset attached below):

NOTE: There were 6 observations read from the data set WORK.HAVE_A.
NOTE: There were 7 observations read from the data set WORK.HAVE_B.
NOTE: The data set WORK.WANT has 0 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

Do you have any sense what is going wrong here?

Thank you very much!

Best regards,

Re: How to create a variable to represent the months of overlapping drug use

jimbarbour — Fri, 25 Sep 2020 02:16:21 GMT

Hi, @CynthiaWei,

Let me take a look at the new data.

Jim

Re: How to create a variable to represent the months of overlapping drug use

jimbarbour — Fri, 25 Sep 2020 04:52:36 GMT

Hi, @CynthiaWei,

Yes, you are correct. A few adjustments were required to accommodate a numeric ID. A numeric ID is actually easier to work with in this case and made the program a little simpler.

Here is the revised code:

%LET	Start_Of_Period		=	'01JAN2017'd;
%LET	End_Of_Period		=	'30APR2018'd;

LIBNAME	DrugDate	XLSX	'.\SampleData\test_CW_20200921_M.xlsx';

DATA	Rx_Dates;
	SET	DrugDate.'#LN00067'n;
RUN;

**------------------------------------------------------------------------------**;

DATA	Rx_Period_Evaluated;
	KEEP	ID	Drug_Date	Both;
	LENGTH	ID						8;
	LENGTH	Drug_Date				8.;
	FORMAT	Drug_Date				MMDDYYS10.;
	LENGTH	End_of_Taking			8.;
	FORMAT	End_of_Taking			MMDDYYS10.;
	LENGTH	Start_of_Period			8.;
	FORMAT	Start_of_Period			MMDDYYS10.;
	LENGTH	End_of_Period			8.;
	FORMAT	End_of_Period			MMDDYYS10.;
	RETAIN	Max_ID					0;

	IF	_N_							=	1							THEN
		DO;
			CALL	MISSING(Drug_Date);
			DECLARE	HASH	Hsh_A_Dates();
			Hsh_A_Dates.DefineKey('ID', 'Drug_Date');
			Hsh_A_Dates.DefineDone();
			CALL	MISSING(Drug_Date);
			DECLARE	HASH	Hsh_B_Dates();
			Hsh_B_Dates.DefineKey('ID', 'Drug_Date');
			Hsh_B_Dates.DefineDone();
		END;

	**	Load drug A hash table.		**;
	DO	WHILE(NOT	End_Of_A);
		SET	Rx_Dates	
			END						=	End_Of_A;
			WHERE	Drug			=	'A';
		IF	ID						>	Max_ID	THEN
			DO;
				Max_ID				=	ID;
			END;
		Drug_Date					=	INTNX('MONTH', Start_Taking_Date, 0);
		End_of_Taking				=	INTNX('MONTH', Last_Taking_Date, 1) - 1;
		DO	WHILE(Drug_Date			<=	End_of_Taking);
			_RC_A					=	Hsh_A_Dates.ADD();
			Drug_Date				=	INTNX('MONTH', Drug_Date, 1);
		END;
	END;

	**	Load drug B hash table.		**;
	DO	WHILE(NOT	End_Of_B);
		SET	Rx_Dates	
			END						=	End_Of_B;
			WHERE	Drug			=	'B';
		IF	ID						>	Max_ID	THEN
			DO;
				Max_ID				=	ID;
			END;
		Drug_Date					=	INTNX('MONTH', Start_Taking_Date, 0);
		End_of_Taking				=	INTNX('MONTH', Last_Taking_Date, 1) - 1;
		DO	WHILE(Drug_Date			<=	End_of_Taking);
			_RC_B					=	Hsh_B_Dates.ADD();
			Drug_Date				=	INTNX('MONTH', Drug_Date, 1);
		END;
	END;

	Start_Of_Period					=	INTNX('MONTH', &Start_Of_Period, 0);
	End_Of_Period					=	INTNX('MONTH', &End_Of_Period, 1) - 1;

	**	Evaluate Drug A and Drug B tables month by month by ID.		**;
	DO	ID							=	1	TO	Max_ID;
		Drug_Date					=	Start_Of_Period;
		DO	WHILE(Drug_Date			<=	End_Of_Period);
			_RC_A					=	Hsh_A_Dates.FIND();
			_RC_B					=	Hsh_B_Dates.FIND();
			IF	Hsh_A_Dates.FIND()	=	0	AND
				Hsh_B_Dates.FIND()	=	0	THEN
				DO;
					Both			=	1;
				END;
			ELSE
				DO;
					Both			=	0;
				END;
			OUTPUT;
			Drug_Date				=	INTNX('MONTH', Drug_Date, 1);
		END;
	END;

	STOP;
RUN;

The results based on the new data are as follows:

I have manually checked the results, and they appear to be correct. If there are any apparent discrepancies, let's go over them.

Jim

Re: How to create a variable to represent the months of overlapping drug use

CynthiaWei — Fri, 25 Sep 2020 08:17:15 GMT

Hi Jim,

I really appreciate your prompt reply a lot!

Your code is working this time!!! I just found a typo that the first prescription for ID=3 should be 8/3/2017 to 9/1/2017 other than 8/3/2018 to 9/1/2018. So sorry for this mistake. Did you use my second definition to calculate the variable Both, that is if drug A and drug B are taken even in 3 days interval (including the scenario when they are actually taken on the same day), it is still considered as an overlapping use of both A and B? If not, that is you are using the first definition, shouldn't it be all 0 for the 16 months period for ID=3?

Best regards,

Re: How to create a variable to represent the months of overlapping drug use

jimbarbour — Fri, 25 Sep 2020 13:16:57 GMT

@CynthiaWei,

I am using the first definition.

Essentially, I am going month by month. If drug A and drug B were both taken any time in a given month, then I am marking them as a "1".

It sounds like I need to go down to the day level and not just the month, yes?

And do you need definition 1 or definition 2? Or do you need a program that can switch back and forth?

Jim

Re: How to create a variable to represent the months of overlapping drug use

CynthiaWei — Fri, 25 Sep 2020 23:25:47 GMT

Hi Jim,

I am sincerely grateful for your continuous advice and help on this project! I appreciate it a lot!

To get back to the question. Yes, we need to go down to the day level. For ID=3, the 1st prescription (drug B) from 8/3/2017 to 9/1/2017 (please update the typo, it should be 2017 but 2018) has No overlapping use with the 2nd prescription (drug A) from 9/3/2017 to 10/2/2017 according to the definition 1. Having both drug A and drug B taking any time in a given month doesn't necessarily make any overlapping use happened. Having both of them in any given day does.

However, it was an overlapping use based on the definition 2 since ID=3 started taking drug A (9/3/2017) within 3 days after the last day of taking drug B (9/1/2017). If the 2nd prescription (drug A) started on 9/5/2017 (not as it is right now on 9/3/2017), which is the 4th day after the last day of taking drug B, then it would NOT be considered as an overlapping use based on the definition 2.

Definition 1:

If interval<=0 day then overlapping use=Yes; if interval>=1 day then overlapping use=0. (Interval is the day difference in start_taking_date and last_taking_date between two different drugs)

Definition 2:

If interval<=3 day then overlapping use=Yes; if interval>=4 day then overlapping use=0

I need two programs, one program for the definition 1 and a separate program for the definition 2.

Best regards,

Re: How to create a variable to represent the months of overlapping drug use

jimbarbour — Sat, 26 Sep 2020 16:11:25 GMT

Hi, @CynthiaWei,

OK, good. I think I understand now.

So here are my results, in two sets. On the left is definition one, with no extra days. On the right, is definition two, with three extra days. Please see what you think.

By the way, these results are both from the same program. I just created a macro variable at the beginning of the program called "extra days." If you set extra days to 0, then you are using definition one. If you set extra days to 3, then you are using definition two. The program is a bit long, so I will post it separately, but if these results look good, you should create additional test data and try running the program.

There are some additional macro variables:

Db - Debug option. Use an '*' (asterisk) for normal runs or blank to include debugging statements in the SAS log.

Start_Of_Period - Start producing results as the month of this date.

End_Of_Period - Stop producing results as the month of this date.

Jim

Re: How to create a variable to represent the months of overlapping drug use

jimbarbour — Sat, 26 Sep 2020 16:22:46 GMT

And here is the SAS code. Please ask questions as needed.

Note: In my autoexec.sas, I have some definitions:

%LET Err1 = ERROR:;

%LET Nte1 = NOTE:;

%LET Nte2 = NOTE-;

Jim

**	Debug option.  Asterisk is no debugging.  Blank for debugging.	**;
%LET	Db									=	;

**	Start producing results as of this month						**;
%LET	Start_Of_Period						=	'01JAN2017'd;

**	Stop producing results as of this month							**;
%LET	End_Of_Period						=	'30APR2018'd;

**	Extend the overlap period by +/- this number of days.			**;
%LET	Extra_Days							=	3;

LIBNAME	DrugDate	XLSX	"&Path\SampleData\test_CW_20200921_M.xlsx";

DATA	Rx_Dates;
	SET	DrugDate.'#LN00067'n;
RUN;

**------------------------------------------------------------------------------**;

DATA	Rx_Period_Evaluated;
	KEEP	ID	Drug_Date	Both;
	LENGTH	ID						8;
	LENGTH	Drug_Date				8.;
	FORMAT	Drug_Date				YYMMD7.;
	LENGTH	End_of_Taking			8.;
	FORMAT	End_of_Taking			MMDDYYS10.;
	LENGTH	Start_of_Period			8.;
	FORMAT	Start_of_Period			MMDDYYS10.;
	LENGTH	End_of_Period			8.;
	FORMAT	End_of_Period			MMDDYYS10.;
	LENGTH	Save_Start_Date			8.;
	FORMAT	Save_Start_Date			MMDDYYS10.;
	LENGTH	Save_End_Date			8.;
	FORMAT	Save_End_Date			MMDDYYS10.;
	LENGTH	Temp_Start_Date			8.;
	FORMAT	Temp_Start_Date			MMDDYYS10.;
	LENGTH	Temp_End_Date			8.;
	FORMAT	Temp_End_Date			MMDDYYS10.;
	RETAIN	Max_ID					0;

	IF	_N_									=	1	THEN
		DO;
			CALL	MISSING							(Drug_Date);
			DECLARE	HASH	Hsh_A_Dates				();
							Hsh_A_Dates.DefineKey 	('ID', 'Drug_Date');
							Hsh_A_Dates.DefineData	('Start_Taking_Date', 'Last_Taking_Date');
							Hsh_A_Dates.DefineDone	();
			DECLARE	HASH	Hsh_B_Dates				();
							Hsh_B_Dates.DefineKey 	('ID', 'Drug_Date');
							Hsh_B_Dates.DefineData	('Start_Taking_Date', 'Last_Taking_Date');
							Hsh_B_Dates.DefineDone	();
		END;

	**	Load drug A hash table.		**;
&Db	PUTLOG	"&Nte2  ";
&Db	PUTLOG	"&Nte1  Loading Drug A";
	DO	WHILE(NOT	End_Of_A);
		SET	Rx_Dates	
			END								=	End_Of_A;
			WHERE	Drug					=	'A';
		Save_Start_Date						=	Start_Taking_Date;
		Save_End_Date						=	Last_Taking_Date;
&Db		PUTLOG	"&Nte2  Start processing "	ID= Save_Start_Date= Save_End_Date= _RC_A=;
		IF	ID								>	Max_ID				THEN
			DO;
&Db				PUTLOG	"&Nte2  Changing Max_ID from to "	Max_ID=	ID=;
				Max_ID						=	ID;
			END;
		Drug_Date							=	INTNX('MONTH', Start_Taking_Date, 0);
		End_of_Taking						=	INTNX('MONTH', Last_Taking_Date, 1) - 1;
&Db		PUTLOG	"&Nte2  "	ID= Drug_Date= End_of_Taking=;
		DO	WHILE(Drug_Date					<=	End_of_Taking);
&Db			PUTLOG	"&Nte2  "	ID=	"Iterate DO WHILE " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
			LINK	Chk_Dates;
			_RC_A							=	Hsh_A_Dates.ADD();
			IF	_RC_A						>	0					THEN
				DO;
&Db					PUTLOG	"&Nte2  "	ID=	" already exists " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
					Needs_Update			=	0;
					LINK	Replace_A;
				END;
			ELSE
				DO;
&Db					PUTLOG	"&Nte2  "	ID=	" Add Successful " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
				END;
&Db			PUTLOG	"&Nte2  ";
			Drug_Date						=	INTNX('MONTH', Drug_Date, 1);
			Start_Taking_Date				=	Save_Start_Date;
			Last_Taking_Date				=	Save_End_Date;
		END;
	END;

	**	Load drug B hash table.		**;
&Db	PUTLOG	"&Nte2  ";
&Db	PUTLOG	"&Nte1  Loading Drug B";
	DO	WHILE(NOT	End_Of_B);
		SET	Rx_Dates	
			END								=	End_Of_B;
			WHERE	Drug					=	'B';
		Save_Start_Date						=	Start_Taking_Date;
		Save_End_Date						=	Last_Taking_Date;
&Db		PUTLOG	"&Nte2  Start processing "	ID= Save_Start_Date= Save_End_Date= _RC_B=;
		IF	ID								>	Max_ID				THEN
			DO;
&Db				PUTLOG	"&Nte2  Changing Max_ID from to "	Max_ID=	ID=;
				Max_ID						=	ID;
			END;
		Drug_Date							=	INTNX('MONTH', Start_Taking_Date, 0);
		End_of_Taking						=	INTNX('MONTH', Last_Taking_Date, 1) - 1;
&Db		PUTLOG	"&Nte2  "	ID= Drug_Date= End_of_Taking=;
		DO	WHILE(Drug_Date					<=	End_of_Taking);
&Db			PUTLOG	"&Nte2  "	ID=	"Iterate DO WHILE " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
			LINK	Chk_Dates;
			_RC_B							=	Hsh_B_Dates.ADD();
			IF	_RC_B						>	0					THEN
				DO;
&Db					PUTLOG	"&Nte2  "	ID=	" already exists " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
					Needs_Update			=	0;
					LINK	Replace_B;
				END;
			ELSE
				DO;
&Db					PUTLOG	"&Nte2  "	ID=	" Add Successful " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
				END;
&Db			PUTLOG	"&Nte2  ";
			Drug_Date						=	INTNX('MONTH', Drug_Date, 1);
			Start_Taking_Date				=	Save_Start_Date;
			Last_Taking_Date				=	Save_End_Date;
		END;
	END;

	Start_Of_Period							=	INTNX('MONTH', &Start_Of_Period, 0);
	End_Of_Period							=	INTNX('MONTH', &End_Of_Period, 1) - 1;

	**	Evaluate Drug A and Drug B tables month by month by ID.		**;
&Db	PUTLOG	"&Nte2  ";
&Db	PUTLOG	"&Nte1  Evaluating Drug A and Drug B tables";
	DO	ID									=	1	TO	Max_ID;
		Drug_Date							=	Start_Of_Period;
		DO	WHILE(Drug_Date					<=	End_Of_Period);
			_RC_A							=	Hsh_A_Dates.FIND();
			Temp_Start_Date					=	Start_Taking_Date;
			Temp_End_Date					=	Last_Taking_Date;
			_RC_B							=	Hsh_B_Dates.FIND();
&Db			PUTLOG	"&Nte1  "	ID= Drug_Date=	End_Of_Period=	_RC_A=	_RC_B=;
			IF	_RC_A						=	0	AND
				_RC_B						=	0	THEN
				DO;
					IF	(Start_Taking_Date	-	&Extra_Days		<=	Temp_Start_Date		<=	Last_Taking_Date	+	&Extra_Days)	OR
						(Temp_Start_Date	-	&Extra_Days		<=	Start_Taking_Date	<=	Temp_End_Date		+	&Extra_Days)	OR
						(Start_Taking_Date	-	&Extra_Days		<=	Temp_End_Date		<=	Last_Taking_Date	+	&Extra_Days)	OR
						(Temp_Start_Date	-	&Extra_Days		<=	Last_Taking_Date	<=	Temp_End_Date		+	&Extra_Days)	THEN
						DO;
							Both			=	1;
&Db							PUTLOG	"&Nte2  Dates within range " ID= Temp_Start_Date= Temp_End_Date= Start_Taking_Date= Last_Taking_Date=;
						END;
					ELSE
						DO;
							Both			=	0;
&Db							PUTLOG	"&Nte2  No match on dates  " ID= Temp_Start_Date= Temp_End_Date= Start_Taking_Date= Last_Taking_Date=;
						END;
				END;
			ELSE
				DO;
					Both					=	0;
&Db					PUTLOG	"&Nte2  Both months not populated " ID= Drug_Date= End_Of_Period= Both= _RC_A= _RC_B=;
				END;
			OUTPUT;
			Drug_Date						=	INTNX('MONTH', Drug_Date, 1);
		END;
	END;

	STOP;

	*********;
	Replace_A:
	*********;
		_RC_A								=	Hsh_A_Dates.FIND();
		IF	_RC_A							>	0					THEN
			DO;
&Db				PUTLOG	"&Nte2  "	ID= " Error on FIND   " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
			END;
		LINK	Updt_Dates;
		IF	Needs_Update											THEN
			DO;
				_RC_A						=	Hsh_A_Dates.REPLACE();
				IF	_RC_A					>	0					THEN
					DO;
&Db						PUTLOG	"&Nte2  "	ID= " Error on replace   " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
					END;
				ELSE
					DO;
&Db						PUTLOG	"&Nte2  "	ID= " Replace successful " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
					END;
			END;
		ELSE
			DO;
&Db				PUTLOG	"&Nte2  "	ID= " No update needed   " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
			END;
	******;
	RETURN;
	******;

	*********;
	Replace_B:
	*********;
		_RC_B								=	Hsh_B_Dates.FIND();
		IF	_RC_B							>	0					THEN
			DO;
				PUTLOG	"&Err1  "	ID= " Error on FIND   " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
			END;
		LINK	Updt_Dates;
		IF	Needs_Update											THEN
			DO;
				_RC_B						=	Hsh_B_Dates.REPLACE();
				IF	_RC_B					>	0					THEN
					DO;
						PUTLOG	"&Err1  "	ID= " Error on replace   " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
					END;
				ELSE
					DO;
&Db						PUTLOG	"&Nte2  "	ID= " Replace successful " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
					END;
			END;
		ELSE
			DO;
&Db				PUTLOG	"&Nte2  "	ID= " No update needed   " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
			END;
	******;
	RETURN;
	******;

	*********;
	Chk_Dates:
	*********;
&Db		PUTLOG	"&Nte2  Chk_Dates Begin "	ID= Start_Taking_Date= Last_Taking_Date= Save_Start_Date= Save_End_Date=;
		IF	Start_Taking_Date				<	Drug_Date			THEN
			Start_Taking_Date				=	Drug_Date;
		IF	Last_Taking_Date				>	INTNX('MONTH', Drug_Date, 1) - 1	THEN
			Last_Taking_Date				=	INTNX('MONTH', Drug_Date, 1) - 1;
&Db		PUTLOG	"&Nte2  Chk_Dates End   "	ID= Start_Taking_Date= Last_Taking_Date= Save_Start_Date= Save_End_Date=;
	******;
	RETURN;
	******;

	**********;
	Updt_Dates:
	**********;
		Temp_Start_Date						=	Start_Taking_Date;
		Temp_End_Date						=	Last_Taking_Date;
		IF	Save_Start_Date					<	Start_Taking_Date	THEN
			DO;
&Db				PUTLOG	"&Nte2  Update start before "	ID= Start_Taking_Date= Last_Taking_Date= Save_Start_Date= Save_End_Date=;
				Start_Taking_Date			=	Save_Start_Date;
				LINK	Chk_Dates;
				IF	Start_Taking_Date		<	Temp_Start_Date		THEN
					Needs_Update			=	1;
&Db				PUTLOG	"&Nte2  Update start after  "	ID= Start_Taking_Date= Last_Taking_Date= Save_Start_Date= Save_End_Date= Needs_Update=;
			END;
		IF	Save_End_Date					>	Last_Taking_Date	THEN
			DO;
&Db				PUTLOG	"&Nte2  Update end before   "	ID= Start_Taking_Date= Last_Taking_Date= Save_Start_Date= Save_End_Date=;
				Last_Taking_Date			=	Save_End_Date;
				LINK	Chk_Dates;
				IF	Last_Taking_Date		>	Temp_End_Date		THEN	
					Needs_Update			=	1;
&Db				PUTLOG	"&Nte2  Update end after    "	ID= Start_Taking_Date= Last_Taking_Date= Save_Start_Date= Save_End_Date= Needs_Update=;
			END;
	******;
	RETURN;
	******;
RUN;

Re: How to create a variable to represent the months of overlapping drug use

CynthiaWei — Sun, 27 Sep 2020 17:19:14 GMT

Hi Jim,

You don't know how much I appreciate your help with my project!!! It really makes my work moving forward!!! I tried your code for both definition 1 and definition 2 based on my dataset, and they all worked very well!

I took your advice to test the code with a new dataset. I am attaching the new dataset here. I found the code for definition 1 worked for the new data very correctly, however, it cannot provide the precise result when applying to definition 2 for ID=4 (for ID=1-3, it worked very well). For example, the overlapping uses for ID=4 should be 2/4/2017 to 3/3/2017 and 5/3/2017, that is the variable Both=1 for 2017-02, 03, and 05. But SAS only returned as 2017-02. I changed the %LET Extra_days=3 when testing the code for definition 2. I resolved this issue by creating a new variable called last_taking_date_Ext=last_taking_date + 3, that is although the actual last taking date is on 2/28/2017 or 4/30/2017, the effect of the drug lasts until 3/3/2017 and 5/3/2017. Is there any way to fix the code to let it more appropriate for my new dataset?

I know you have generously spent your time on my project for very long time, I sincerely appreciate your great advice and help!!!

Best regards,

Re: How to create a variable to represent the months of overlapping drug use

jimbarbour — Sun, 27 Sep 2020 17:47:19 GMT

@CynthiaWei,

Let me take another look at the code when I get home. I coded things with hard month boundaries. Maybe for the extended periods I need to be more flexible with the month boundaries.

Jim

Re: How to create a variable to represent the months of overlapping drug use

jimbarbour — Mon, 28 Sep 2020 06:33:52 GMT

@CynthiaWei,

I've got my program mostly working, but one month for ID 4 disagrees with what I think it should be.

When you have a moment, would you look over the following table that I put together by hand. This is what I think my program should output (once I get it debugged). Would you verify that the Definition 1 and Definition 2 values (0 or 1) are correct? If they're not correct, would you please maybe make a note of why a particular value is incorrect? That would be super helpful.

Thank you,

Jim

Re: How to create a variable to represent the months of overlapping drug use

jimbarbour — Mon, 28 Sep 2020 23:12:43 GMT

Hi, @CynthiaWei,

I think I've got everything working now. Sorry this took a while. Busy weekend and busy at work right now. I have to fit SAS community coding in where I can.

Here are my results which I believe match the requirements. Definition 1 is on the left. Definition 2 is on the right. I'll post the code below that. Please let me know if this works for you.

Jim

**	Debug option.  Asterisk is no debugging.  Blank for debugging.	**;
%LET	Db									=	;

**	Start producing results as of this month						**;
%LET	Start_Of_Period						=	'01JAN2017'd;

**	Stop producing results as of this month							**;
%LET	End_Of_Period						=	'30APR2018'd;

**	Extend the overlap period by +/- this number of days.			**;
%LET	Extra_Days							=	3;

%LET	Extended_Data						=	NO;
*%LET	Extended_Data						=	YES;

**------------------------------------------------------------------------------**;

*LIBNAME 	DrugDate	XLSX	"&Path\SampleData\test_CW_20200921_M.xlsx";
LIBNAME 	DrugDate	XLSX	"&Path\SampleData\test_CW_20200926_M.xlsx";

**------------------------------------------------------------------------------**;

DATA	Rx_Dates;
	SET	DrugDate.'#LN00067'n;
RUN;

**------------------------------------------------------------------------------**;

DATA	Rx_Period_Evaluated	(KEEP=ID 
		%IF	%UPCASE(&Extended_Data)	=	YES	%THEN
			%DO;
				A_Start_Date A_End_Date B_Start_Date B_End_Date 
			%END;
		Drug_Date Both)
	A_Dates	(KEEP=ID Drug_Date Start_Taking_Date Last_Taking_Date)
	B_Dates	(KEEP=ID Drug_Date Start_Taking_Date Last_Taking_Date)
	;
	
	LENGTH	ID						8;
	LENGTH	Drug_Date				8.;
	FORMAT	Drug_Date				YYMMDDS10.;
	FORMAT	Save_Drug_Date			YYMMDDS10.;
	LENGTH	End_of_Taking			8.;
	FORMAT	End_of_Taking			MMDDYYS10.;
	LENGTH	Start_of_Period			8.;
	FORMAT	Start_of_Period			MMDDYYS10.;
	LENGTH	End_of_Period			8.;
	FORMAT	End_of_Period			MMDDYYS10.;
	LENGTH	Save_Start_Date			8.;
	FORMAT	Save_Start_Date			MMDDYYS10.;
	LENGTH	Save_End_Date			8.;
	FORMAT	Save_End_Date			MMDDYYS10.;
	LENGTH	Temp_Start_Date			8.;
	FORMAT	Temp_Start_Date			MMDDYYS10.;
	LENGTH	Temp_End_Date			8.;
	FORMAT	Temp_End_Date			MMDDYYS10.;
	FORMAT	A_Start_Date			MMDDYYS10.;
	FORMAT	A_End_Date				MMDDYYS10.;
	FORMAT	B_Start_Date			MMDDYYS10.;
	FORMAT	B_End_Date				MMDDYYS10.;


	RETAIN	Max_ID					0;
	RETAIN	Extra_Days				0;

	**------------------------------**;
	**	Define hash tables.			**;
	**------------------------------**;
	IF	_N_									=	1	THEN
		DO;
			Extra_Days						=	&Extra_Days;
			CALL	MISSING							(Drug_Date);
			DECLARE	HASH	Hsh_A_Dates				(ORDERED: 'A', MULTIDATA: 'N');
							Hsh_A_Dates.DefineKey 	('ID', 'Drug_Date');
							Hsh_A_Dates.DefineData	('ID', 'Drug_Date', 'Start_Taking_Date', 'Last_Taking_Date');
							Hsh_A_Dates.DefineDone	();
			DECLARE	HITER	Hit_A_Dates('Hsh_A_Dates');
			DECLARE	HASH	Hsh_B_Dates				(ORDERED: 'A', MULTIDATA: 'N');
							Hsh_B_Dates.DefineKey 	('ID', 'Drug_Date');
							Hsh_B_Dates.DefineData	('ID', 'Drug_Date', 'Start_Taking_Date', 'Last_Taking_Date');
							Hsh_B_Dates.DefineDone	();
			DECLARE	HITER	Hit_B_Dates('Hsh_B_Dates');
		END;

	**------------------------------**;
	**	Load drug A hash table.		**;
	**------------------------------**;
&Db	PUTLOG	"&Nte2  ";
&Db	PUTLOG	"&Nte1  Loading Drug A";
	DO	WHILE(NOT	End_Of_A);
		SET	Rx_Dates	
			END								=	End_Of_A;
			WHERE	Drug					=	'A';
		Save_Start_Date						=	Start_Taking_Date;
		Save_End_Date						=	Last_Taking_Date;
&Db		PUTLOG	"&Nte2  Start processing "	ID= Save_Start_Date= Save_End_Date= _RC_A=;
		IF	ID								>	Max_ID				THEN
			DO;
&Db				PUTLOG	"&Nte2  Changing Max_ID from to "	Max_ID=	ID=;
				Max_ID						=	ID;
			END;
		Drug_Date							=	INTNX('MONTH', Start_Taking_Date, 0);
		End_of_Taking						=	INTNX('MONTH', Last_Taking_Date, 1) - 1;
&Db		PUTLOG	"&Nte2  "	ID= Drug_Date= End_of_Taking=;
		DO	WHILE(Drug_Date					<=	End_of_Taking);
&Db			PUTLOG	"&Nte2  "	ID=	"Iterate DO WHILE " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
			LINK	Chk_Dates;
			_RC_A							=	Hsh_A_Dates.ADD();
			IF	_RC_A						>	0					THEN
				DO;
&Db					PUTLOG	"&Nte2  "	ID=	" already exists " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
					Needs_Update			=	0;
					LINK	Replace_A;
				END;
			ELSE
				DO;
&Db					PUTLOG	"&Nte2  "	ID=	" Add Successful " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
				END;
&Db			PUTLOG	"&Nte2  ";
			Drug_Date						=	INTNX('MONTH', Drug_Date, 1);
			Start_Taking_Date				=	Save_Start_Date;
			Last_Taking_Date				=	Save_End_Date;
		END;
	END;

	**------------------------------**;
	**	Load drug B hash table.		**;
	**------------------------------**;
&Db	PUTLOG	"&Nte2  ";
&Db	PUTLOG	"&Nte1  Loading Drug B";
	DO	WHILE(NOT	End_Of_B);
		SET	Rx_Dates	
			END								=	End_Of_B;
			WHERE	Drug					=	'B';
		Save_Start_Date						=	Start_Taking_Date;
		Save_End_Date						=	Last_Taking_Date;
&Db		PUTLOG	"&Nte2  Start processing "	ID= Save_Start_Date= Save_End_Date= _RC_B=;
		IF	ID								>	Max_ID				THEN
			DO;
&Db				PUTLOG	"&Nte2  Changing Max_ID from to "	Max_ID=	ID=;
				Max_ID						=	ID;
			END;
		Drug_Date							=	INTNX('MONTH', Start_Taking_Date, 0);
		End_of_Taking						=	INTNX('MONTH', Last_Taking_Date, 1) - 1;
&Db		PUTLOG	"&Nte2  "	ID= Drug_Date= End_of_Taking=;
		DO	WHILE(Drug_Date					<=	End_of_Taking);
&Db			PUTLOG	"&Nte2  "	ID=	"Iterate DO WHILE " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
			LINK	Chk_Dates;
			_RC_B							=	Hsh_B_Dates.ADD();
			IF	_RC_B						>	0					THEN
				DO;
&Db					PUTLOG	"&Nte2  "	ID=	" already exists " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
					Needs_Update			=	0;
					LINK	Replace_B;
				END;
			ELSE
				DO;
&Db					PUTLOG	"&Nte2  "	ID=	" Add Successful " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
				END;
&Db			PUTLOG	"&Nte2  ";
			Drug_Date						=	INTNX('MONTH', Drug_Date, 1);
			Start_Taking_Date				=	Save_Start_Date;
			Last_Taking_Date				=	Save_End_Date;
		END;
	END;

	**--------------------------------------------------------------**;
	**	Initialize the start and end of the period of interest.		**;
	**--------------------------------------------------------------**;
	Start_Of_Period							=	INTNX('MONTH', &Start_Of_Period, 0);
	End_Of_Period							=	INTNX('MONTH', &End_Of_Period, 1) - 1;

	**--------------------------------------------------------------**;
	**	Create SAS data sets from drug tables for QC purposes.		**;
	**--------------------------------------------------------------**;
	_RC_A									=	0;
	ID										=	0;
	Drug_Date								=	Start_Of_Period;
	_RC_A									=	Hit_A_Dates.FIRST();
	DO	WHILE	(_RC_A						=	0);
		OUTPUT	A_Dates;
		_RC_A								=	Hit_A_Dates.NEXT();
	END;

	_RC_B									=	0;
	ID										=	0;
	Drug_Date								=	Start_Of_Period;
	_RC_B									=	Hit_B_Dates.FIRST();
	DO	WHILE	(_RC_B						=	0);
		OUTPUT	B_Dates;
		_RC_B								=	Hit_B_Dates.NEXT();
	END;

	**--------------------------------------------------------------**;
	**	Evaluate Drug A and Drug B tables month by month by ID.		**;
	**--------------------------------------------------------------**;
&Db	PUTLOG	"&Nte2  ";
&Db	PUTLOG	"&Nte1  Evaluating Drug A and Drug B tables";
	DO	ID									=	1	TO	Max_ID;
		Drug_Date							=	Start_Of_Period;
		DO	WHILE(Drug_Date					<=	End_Of_Period);
			_RC_A							=	Hsh_A_Dates.FIND();
			IF	_RC_A						=	0	THEN
				DO;
					A_Start_Date			=	Start_Taking_Date;
					A_End_Date				=	Last_Taking_Date;
				END;
			ELSE
				DO;
					CALL	MISSING(A_Start_Date, A_End_Date);
				END;
			_RC_B							=	Hsh_B_Dates.FIND();
			IF	_RC_B						=	0	THEN
				DO;
					B_Start_Date			=	Start_Taking_Date;
					B_End_Date				=	Last_Taking_Date;
				END;
			ELSE
				DO;
					CALL	MISSING(B_Start_Date, B_End_Date);
				END;
&Db			PUTLOG	"&Nte1  "	ID= Drug_Date=	End_Of_Period=	_RC_A=	_RC_B=;
			IF	_RC_A						=	0	OR
				_RC_B						=	0	THEN
				DO;
					IF	_RC_A				>	0	AND
						Extra_Days			>	0	THEN
						DO;
							LINK	A_Not_Found;
						END;
					ELSE
					IF	_RC_B				>	0	AND
						Extra_Days			>	0	THEN
						DO;
							LINK	B_Not_Found;
						END;
					IF	NOT NMISS(B_Start_Date, A_Start_Date, B_End_Date, Extra_Days)								AND
						(B_Start_Date		-	Extra_Days	<=	A_Start_Date	<=	B_End_Date	+	Extra_Days)		THEN
						DO;
							Both			=	1;
&Db							PUTLOG	"&Nte2  Both.  A_Start between B_Start and B_Stop     " ID= A_Start_Date= A_End_Date= B_Start_Date= B_End_Date= Extra_Days=;
						END;
					ELSE
					IF	NOT NMISS(A_Start_Date, Extra_Days, B_Start_Date, A_End_Date)								AND
						(A_Start_Date		-	Extra_Days	<=	B_Start_Date	<=	A_End_Date	+	Extra_Days)		THEN
						DO;
							Both			=	1;
&Db							PUTLOG	"&Nte2  Both.  B_Start between A_Start and A_End " ID= A_Start_Date= A_End_Date= B_Start_Date= B_End_Date= Extra_Days=;
						END;
					ELSE
					IF	NOT NMISS(B_Start_Date, Extra_Days, A_End_Date, B_End_Date, Extra_Days)						AND
						(B_Start_Date		-	Extra_Days	<=	A_End_Date		<=	B_End_Date	+	Extra_Days)		THEN
						DO;
							Both			=	1;
&Db							PUTLOG	"&Nte2  Both.  A_End between B_start and B_stop       " ID= A_Start_Date= A_End_Date= B_Start_Date= B_End_Date= Extra_Days=;
						END;
					ELSE
					IF	NOT NMISS(A_Start_Date, Extra_Days, B_End_Date, A_End_Date, Extra_Days)						AND
						(A_Start_Date		-	Extra_Days	<=	B_End_Date		<=	A_End_Date	+	Extra_Days)		THEN
						DO;
							Both			=	1;
&Db							PUTLOG	"&Nte2  Both.  B_End between A_Start and A_End   " ID= A_Start_Date= A_End_Date= B_Start_Date= B_End_Date= Extra_Days=;
						END;
					ELSE
						DO;
							Both			=	0;
&Db							PUTLOG	"&Nte2  No match on dates  " ID= A_Start_Date= A_End_Date= B_Start_Date= B_End_Date=;
						END;
				END;
			ELSE
				DO;
					Both					=	0;
&Db					PUTLOG	"&Nte2  Both months not populated " ID= Drug_Date= End_Of_Period= Both= _RC_A= _RC_B=;
				END;
			OUTPUT	Rx_Period_Evaluated;
			Drug_Date						=	INTNX('MONTH', Drug_Date, 1);
		END;
	END;

	STOP;

	*********;
	Replace_A:
	*********;
		_RC_A								=	Hsh_A_Dates.FIND();
		IF	_RC_A							>	0					THEN
			DO;
&Db				PUTLOG	"&Nte2  "	ID= " Error on FIND   " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
			END;
		LINK	Updt_Dates;
		IF	Needs_Update											THEN
			DO;
				_RC_A						=	Hsh_A_Dates.REPLACE();
				IF	_RC_A					>	0					THEN
					DO;
&Db						PUTLOG	"&Nte2  "	ID= " Error on replace   " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
					END;
				ELSE
					DO;
&Db						PUTLOG	"&Nte2  "	ID= " Replace successful " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
					END;
			END;
		ELSE
			DO;
&Db				PUTLOG	"&Nte2  "	ID= " No update needed   " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_A=;
			END;
	******;
	RETURN;
	******;

	*********;
	Replace_B:
	*********;
		_RC_B								=	Hsh_B_Dates.FIND();
		IF	_RC_B							>	0					THEN
			DO;
				PUTLOG	"&Err1  "	ID= " Error on FIND   " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
			END;
		LINK	Updt_Dates;
		IF	Needs_Update											THEN
			DO;
				_RC_B						=	Hsh_B_Dates.REPLACE();
				IF	_RC_B					>	0					THEN
					DO;
						PUTLOG	"&Err1  "	ID= " Error on replace   " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
					END;
				ELSE
					DO;
&Db						PUTLOG	"&Nte2  "	ID= " Replace successful " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
					END;
			END;
		ELSE
			DO;
&Db				PUTLOG	"&Nte2  "	ID= " No update needed   " Drug_Date= Start_Taking_Date= Last_Taking_Date= End_of_Taking= _RC_B=;
			END;
	******;
	RETURN;
	******;

	*********;
	Chk_Dates:
	*********;
&Db		PUTLOG	"&Nte2  Chk_Dates Begin "	ID= Start_Taking_Date= Last_Taking_Date= Save_Start_Date= Save_End_Date=;
		IF	Start_Taking_Date				<	Drug_Date			THEN
			Start_Taking_Date				=	Drug_Date;
		IF	Last_Taking_Date				>	INTNX('MONTH', Drug_Date, 1) - 1	THEN
			Last_Taking_Date				=	INTNX('MONTH', Drug_Date, 1) - 1;
&Db		PUTLOG	"&Nte2  Chk_Dates End   "	ID= Start_Taking_Date= Last_Taking_Date= Save_Start_Date= Save_End_Date=;
	******;
	RETURN;
	******;

	**********;
	Updt_Dates:
	**********;
		Temp_Start_Date						=	Start_Taking_Date;
		Temp_End_Date						=	Last_Taking_Date;
		IF	Save_Start_Date					<	Start_Taking_Date	THEN
			DO;
&Db				PUTLOG	"&Nte2  Update start before "	ID= Start_Taking_Date= Last_Taking_Date= Save_Start_Date= Save_End_Date=;
				Start_Taking_Date			=	Save_Start_Date;
				LINK	Chk_Dates;
				IF	Start_Taking_Date		<	Temp_Start_Date		THEN
					Needs_Update			=	1;
&Db				PUTLOG	"&Nte2  Update start after  "	ID= Start_Taking_Date= Last_Taking_Date= Save_Start_Date= Save_End_Date= Needs_Update=;
			END;
		IF	Save_End_Date					>	Last_Taking_Date	THEN
			DO;
&Db				PUTLOG	"&Nte2  Update end before   "	ID= Start_Taking_Date= Last_Taking_Date= Save_Start_Date= Save_End_Date=;
				Last_Taking_Date			=	Save_End_Date;
				LINK	Chk_Dates;
				IF	Last_Taking_Date		>	Temp_End_Date		THEN	
					Needs_Update			=	1;
&Db				PUTLOG	"&Nte2  Update end after    "	ID= Start_Taking_Date= Last_Taking_Date= Save_Start_Date= Save_End_Date= Needs_Update=;
			END;
	******;
	RETURN;
	******;

	***********;
	A_Not_Found:
	***********;
		**	Save keys.	**;
		Save_Drug_Date						=	Drug_Date;

		**	Get start date from prior end date.	**;
		Drug_Date							=	INTNX('MONTH', Drug_Date, -1);
		_RC_A								=	Hsh_A_Dates.FIND();
		IF	_RC_A							=	0	THEN
			DO;
&Db				PUTLOG	"&Nte2  Prior A found     "	ID= Drug_Date= Start_Taking_Date= Last_Taking_Date= A_Start_Date= A_End_Date=;
				A_Start_Date				=	Last_Taking_Date;
			END;
		ELSE
			DO;
&Db				PUTLOG	"&Nte2  Prior A not found "	ID= Drug_Date= Start_Taking_Date= Last_Taking_Date= A_Start_Date= A_End_Date=;
				CALL	MISSING(A_Start_Date);
			END;
		Drug_Date							=	Save_Drug_Date;

		**	Get end date from next start date.	**;
		Drug_Date							=	INTNX('MONTH', Drug_Date, 1);
		_RC_A								=	Hsh_A_Dates.FIND();
		IF	_RC_A							=	0	THEN
			DO;
&Db				PUTLOG	"&Nte2  Next A found      "	ID= Drug_Date= Start_Taking_Date= Last_Taking_Date= A_Start_Date= A_End_Date=;
				A_End_Date					=	Start_Taking_Date;
			END;
		ELSE
			DO;
&Db				PUTLOG	"&Nte2  Next A not found  "	ID= Drug_Date= Start_Taking_Date= Last_Taking_Date= A_Start_Date= A_End_Date=;
				CALL	MISSING(A_End_Date);
			END;
		Drug_Date							=	Save_Drug_Date;
	******;
	RETURN;
	******;

	***********;
	B_Not_Found:
	***********;
		**	Save keys.	**;
		Save_Drug_Date						=	Drug_Date;

		**	Get start date from prior end date.	**;
		Drug_Date							=	INTNX('MONTH', Drug_Date, -1);
		_RC_B								=	Hsh_B_Dates.FIND();
		IF	_RC_B							=	0	THEN
			DO;
&Db				PUTLOG	"&Nte2  Prior B found     "	ID= Drug_Date= Start_Taking_Date= Last_Taking_Date= B_Start_Date= B_End_Date=;
				B_Start_Date				=	Last_Taking_Date;
			END;
		ELSE
			DO;
&Db				PUTLOG	"&Nte2  Prior B not found "	ID= Drug_Date= Start_Taking_Date= Last_Taking_Date= B_Start_Date= B_End_Date=;
				CALL	MISSING(B_Start_Date);
			END;
		Drug_Date							=	Save_Drug_Date;

		**	Get end date from next start date.	**;
		Drug_Date							=	INTNX('MONTH', Drug_Date, 1);
		_RC_B								=	Hsh_B_Dates.FIND();
		IF	_RC_B							=	0	THEN
			DO;
&Db				PUTLOG	"&Nte2  Next B found      "	ID= Drug_Date= Start_Taking_Date= Last_Taking_Date= B_Start_Date= B_End_Date=;
				B_End_Date					=	Start_Taking_Date;
			END;
		ELSE
			DO;
&Db				PUTLOG	"&Nte2  Next B not found  "	ID= Drug_Date= Start_Taking_Date= Last_Taking_Date= B_Start_Date= B_End_Date=;
				CALL	MISSING(B_End_Date);
			END;
		Drug_Date							=	Save_Drug_Date;
	******;
	RETURN;
	******;
RUN;

**------------------------------------------------------------------------------**;

Re: How to create a variable to represent the months of overlapping drug use

jimbarbour — Tue, 29 Sep 2020 18:25:15 GMT

@CynthiaWei,

Did that latest version of the code work? It should be able to accommodate all of the conditions in the data I've seen so far.

Jim

Re: How to create a variable to represent the months of overlapping drug use

CynthiaWei — Wed, 30 Sep 2020 02:27:02 GMT

Hi Jim,

I am so sorry for my late response.

I think your code that was posted on 9/26/2020 12:22pm matches best with my desired result. The only issue with this code is that the ID in my test data is from 1 and it is continuous, 1, 2, 3, and 4. However, the ID in my real data is not continuous and not start from 1. It is like 6, 10, 21, 23, 24, 29, and so on. How can I fix the code to accommodate it to my real data in terms of ID? Thank you so much!

Back to the table you put together by hand (I really appreciate that you did this for checking!!!). I think we have no problem with definition 1 since it is straightforward. I know definition 2 is very tricky and needs to have a better explanation. Let think in this way for definition 2.Drug A and B are not allowed to take simultaneously based on clinical guideline. Both drug A and B have a delayed effect (not an effect that could affect the previous drug), which means that blood concentration for each of them can still be tested even 3 days AFTER the last taking date of the other drug, whichever drug takes first. For ID=1, the actual effect for drug B is from 1/1/2017 to 7/2/2017 (3days after 6/29/2017). The actual effect of drug A of the 2nd prescription for ID=1 is from 2/1/2017 to 4/4/4/2017, the 3rd is from 3/1/2017 to 5/2/2017 (3 days after 4/29), and the 4th is from 4/1/2017 to 6/2/2017 (3 days after 5/20/2017). The actual period that drug B actually had effect on ID=1 covered the period that drug A was taking, and the overlapping use happened in 2017-02, 03, 04, 05, and 06, but not 2017-01. This is because ID=1 only took drug B but not drug A, so there was no overlapping use happened in 2017-01. So, same for ID=4, the 1st prescription should be considered from 1/4/2017 to 3/3/2017, and the 3rd prescription should be from 3/7/2017 to 5/3/2017, so the overlapping use for ID=1 happened in 2017-02, 03, and 05. For ID=2, I think the overlapping use happened in 2017-06 and 07. For ID=3, I think the overlapping use happened in 2017-09, 10, 11 and 12. Do you agree with me?

I think one solution is to create a new variable titled last_taking_date_Ext by simply adding 3 to last_taking_take (last_taking_date_Ext=last_taking_date + 3), and this last_taking_date_Ext that represents the actual last date for a specific drug is going to be replaced all the last_taking_date in the code when applying definition 2. Does it make sense?

Many many thanks for your effort and time again!!! I really this great learning opportunity!!!

Best regards,