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
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;
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.