Hi, I'm dealting with two sets of date segments in a single data set. Please find the example below:
MEM_ID VOID_BGN VOID_END ACT_BGN ACT_END
H 01JUN2015 31MAY2016 01JUN2015 30JUN2015
H 01JUN2015 31MAY2016 01JUL2015 29FEB2016
H 01JUN2015 31MAY2016 01MAR2016 31MAR2016
H 01JUN2015 31MAY2016 01APR2016 30APR2016
H 01JUN2015 31MAY2016 01MAY2016 31MAY2016
H 01MAR2016 31MAY2016 01MAR2016 31MAR2016
H 01MAR2016 31MAY2016 01APR2016 30APR2016
H 01MAR2016 31MAY2016 01MAY2016 31MAY2016
I have two date segments in this data set in the form of void and active. For each MEM_ID I'm looking for active date segments that cover the void date segments.
In this case void date segments has two date ranges 01JUN2015 to 31MAY2016 and 01MAR2016 to 31MAY2016 and they both are covered by the continuous active segments for this particular MEM_ID. I'm unable to identify such scenarios in my main data set because the date segments are divided and I've failed to create a logic to identify such MEM_ID's. There are many such cases in my main data set. Could you please help me with the code.
My desired output should be:
MEM_ID VOID_BGN VOID_END ACT_BGN ACT_END
H 01JUN2015 31MAY2016 01JUN2015 31MAY2016
H 01MAR2016 31MAY2016 01MAR2016 31MAY2016
Thank you for your help.
The following will work with your test data:
data want (drop=_:); set have (rename=(act_bgn=_act_bgn)); format ACT_BGN date9.; retain ACT_BGN; by MEM_ID; set have ( firstobs = 2 keep = act_bgn rename = (act_bgn = _Next_act_bgn) ) have ( obs = 1 drop = _all_ ); if first.MEM_ID then act_bgn=_act_bgn; if act_end+1 ne _Next_act_bgn then do; output; act_bgn=_Next_act_bgn; end; run;
HTH,
Art, CEO, AnalystFinder.com
The following will work with your test data:
data want (drop=_:); set have (rename=(act_bgn=_act_bgn)); format ACT_BGN date9.; retain ACT_BGN; by MEM_ID; set have ( firstobs = 2 keep = act_bgn rename = (act_bgn = _Next_act_bgn) ) have ( obs = 1 drop = _all_ ); if first.MEM_ID then act_bgn=_act_bgn; if act_end+1 ne _Next_act_bgn then do; output; act_bgn=_Next_act_bgn; end; run;
HTH,
Art, CEO, AnalystFinder.com
Thank you so much. This works brilliantly.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.