BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASTad
Fluorite | Level 6

Hello,

 

I have two data sets, each containg 3 fields.

 

Data Set A

 

ID     Void_BGN_DT   Void_END_DT

A        JULY 1 2015       July31 2015

.

.

Data Set B

 

ID     Active_BGN_DT   Active_END_DT

A         July1 2015               July15 2015

A         July16 2015              July31 2015

 

 

I want to combine the date segments in Data set B  since they are continuous and replace the void date segment in data set A.

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

Ah, I see you posted this three times. My answer hasn't changed:

 

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;

 

Art, CEO, AnalystFinder.com

View solution in original post

6 REPLIES 6
Reeza
Super User

 

Your question appears to have two components:

1. Identify and create continuous periods of enrollment for a particular individual/policy

2. Update data in a second dataset based on information from Part #1.

 

For solution to #1, search for 'continuous enrollment' on here you'll find many answered questions identical to your situation.

 

For the second part, please clarify how you the record would be updated. 

SASTad
Fluorite | Level 6

Hi,

I will rephrase my question here. 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.

Reeza
Super User

Here's a solved question from searching for 'continuous enrollment' that's pretty much exactly what you want. 

 

 

art297
Opal | Level 21

Ah, I see you posted this three times. My answer hasn't changed:

 

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;

 

Art, CEO, AnalystFinder.com

SASTad
Fluorite | Level 6

Sorry, it got posted twice by mistake and the third post was a simplified version. Thank you for your help.

Reeza
Super User

@SASTad Pleae mark ALL posts answered now. 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1138 views
  • 3 likes
  • 3 in conversation