2017-08-02 21:20:03 UTC
I am having trouble with many to many merge and I hope some of you can help me out. Here is the situation:
In conmed dataset below I want to add a column Cycle from Cycle dataset that follow by comparing conmded.CMSTDT to cycle.STARTDT and cycle.ENDDT.
So for example, fort he first record, the value of Cycle would be 'Cycle 1' since CMSTDT fall between startdt-enddt of Cycle 1 and so on. For last record the Cycle should be missing since CMSTDT doesnt fall within the range
The final dataset should only have all the records from conmed plus the Cycle column.
input subjid $1-3 cmtrt $5-9 cmstdt $11-20;
001 TRT1 2017-01-01
001 TRT2 2017-01-06
001 TRT4 2017-03-26
001 TRT5 2017-02-16
001 TRT5 2017-08-01
input subjid $1-3 cycle $5-10 startdt $12-21 enddt $23-32;
001 Cycle1 2017-01-01 2017-01-08
001 Cycle2 2017-02-10 2017-02-18
001 Cycle3 2017-03-20 2017-03-30
Here is what I have tried so far.
I created a cartesian product via proc sql. In 2nd proc sql step i only kept records where CMSTDT is between start-enddt from cycle dataset. But that will eliminate the last record from conmed.