How to Locate Where a Date Should Fall in a Vertacle Date Range

[djbateman]

I have a scenario that I have been coming across more and more often.  I figured now is the time to see if there is a more efficient way to do what I want.

Suppose I have 2 tables: an adverse event table and a visit table (see below for examples).  I might want to take the date that an AE occurred (AEDT) and see in which cycle of the study the AE occurred.

SUBJID	  AEDT	        TERM
001-001	  1/7/2016	Nausea
001-001	  1/9/2016	Headache
001-001	  2/4/2016	Vomiting

SUBJID	VISITDT	        TIMEPT	CYCLE	DAY
001-001	1/4/2016	C1D1	1	1
001-001	1/11/2016	C1D8	1	8
001-001	1/18/2016	C1D15	1	15
001-001	1/25/2016	C2D1	2	1
001-001	2/1/2016	C2D8	2	8
001-001	2/8/2016	C2D15	2	15

 
From the info above, I can eyeball that the first 2 events occurred in Cycle 1 while the third event occurred in Cycle 2.  Would SQL help me find those proper placements?  Currently, I am taking the visit table and transposing it so that there is a column for each visit (just keeping the CxD1 dates).  Then I merge it in with the AE table (see example table below) and loop through every visit column and check if AEDT is between [C1D1 and C2D1) or [C2D1 and C3D1) (and so on for each cycle visit available).  This seems very ineffective.  Is there a better way?

SUBJID	AEDT	   TERM	        C1D1	   C2D1
001-001	1/7/2016   Nausea	1/4/2016   1/25/2016
001-001	1/9/2016   Headache	1/4/2016   1/25/2016
001-001	2/4/2016   Vomiting	1/4/2016   1/25/2016

[PGStats · Accepted solution]

You could merge the two datasets:

/* Assume both datasets are sorted by SUBJID and date */
data eventCycles;
retain currentCycle;
merge 
    events(rename=AEDT=date in=event)
    visits(rename=VISITDT=date);
by SUBJID date;
if first.SUBJID then call missing(currentCycle); /* In case there is no visit */
if not missing(cycle) then currentCycle = cycle;
if event then output;
keep SUBJID date currentCycle TERM; 
run;