Discussion:
Many to many merge to get cycle information
Add Reply
KD S
2017-08-02 21:20:03 UTC
Reply
Permalink
Raw Message
Hi Friends,
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.

data conmed;
input subjid $1-3 cmtrt $5-9 cmstdt $11-20;
datalines;
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
;
run;

data cycle;
input subjid $1-3 cycle $5-10 startdt $12-21 enddt $23-32;
datalines;
001 Cycle1 2017-01-01 2017-01-08
001 Cycle2 2017-02-10 2017-02-18
001 Cycle3 2017-03-20 2017-03-30
; run;

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.

Many thanks.
Kenneth M. Lin
2017-08-09 01:38:55 UTC
Reply
Permalink
Raw Message
Where are your SQL statements?

"KD S" wrote in message news:50b21126-b624-49ab-83dd-***@googlegroups.com...

Hi Friends,
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.

data conmed;
input subjid $1-3 cmtrt $5-9 cmstdt $11-20;
datalines;
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
;
run;

data cycle;
input subjid $1-3 cycle $5-10 startdt $12-21 enddt $23-32;
datalines;
001 Cycle1 2017-01-01 2017-01-08
001 Cycle2 2017-02-10 2017-02-18
001 Cycle3 2017-03-20 2017-03-30
; run;

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.

Many thanks.

Loading...