Date Concatenation While Making New Variables

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Date Concatenation While Making New Variables

I am trying to construct episodes of coverage for patients in an insurance database. My episodes of coverage are continuous periods of time that a patient was enrolled in an insurance plan. This is what I have:

ID   YEAR   ENRIND1-ENRIND12   

101 2008    0 0 1 1 1 1 1 1 1 1 1 1 

101 2009    1 1 1 1 1 0 0 1 1 1 1 1  

101 2010    1 1 1 1 1 1 1 1 1 0 0 0 

What I want to make is two episodes of continuous coverage for this:

ID     FIRST1     LAST1      FIRST2      LAST2      …  FIRSTN    LASTN

101  2/1/2008  5/31/2009    8/1/2009   9/30/2010

Complications being that multiple observations of IDs are necessary for other parts of the analysis and I think it will be quite fine to end up with something like this:

ID     FIRST1     LAST1      FIRST2      LAST2      …  FIRSTN    LASTN

101  2/1/2008  5/31/2009    8/1/2009   9/30/2010

101  2/1/2008  5/31/2009    8/1/2009   9/30/2010

101  2/1/2008  5/31/2009    8/1/2009   9/30/2010

I added the FIRSTN LASTN because some people may have > 2 periods of continuous coverage.

Any assistance here is appreciated, I only wish I could repay in some way.

Thank you in advance.


Accepted Solutions
Solution
‎03-21-2014 10:41 PM
Super User
Super User
Posts: 7,039

Re: Date Concatenation While Making New Variables

Either make it totally vertical and scan for blocks. Or create one long string with 12 characters for each year and use FINDW() to scan across it.

Here is an example using the vertical structure.  (Note to improve performance and save space you could make the VERTICIAL dataset a view.)

data have ;

  input ID   YEAR   ENRIND1-ENRIND12 ;

cards;

101 2008    0 0 1 1 1 1 1 1 1 1 1 1

101 2009    1 1 1 1 1 0 0 1 1 1 1 1

101 2010    1 1 1 1 1 1 1 1 1 0 0 0

run;

data vertical ;

  set have ;

  by id year;

  array m enrind1-enrind12 ;

  do month=1 to 12 ;

    coverage = m(month);

    output;

  end;

run;

data blocks ;

  do until (last.coverage);

    set vertical ;

    by id coverage notsorted ;

    if first.id then interval=0;

    if first.coverage then do;

       interval + 1;

       start = mdy(month,1,year);

    end;

    if last.coverage then do;

       end = intnx('month',mdy(month,1,year),0,'E') ;

       months = intck('month',start,end)+1;

       output;

    end;

  end;

  keep id start end coverage interval months;

  format start end date9.;

run;

proc print;

run;

Obs     ID    coverage    interval        start          end    months

1     101        0           1       01JAN2008    29FEB2008       2

2     101        1           2       01MAR2008    31MAY2009      15

3     101        0           3       01JUN2009    31JUL2009       2

4     101        1           4       01AUG2009    30SEP2010      14

5     101        0           5       01OCT2010    31DEC2010       3

View solution in original post


All Replies
Super Contributor
Posts: 644

Re: Date Concatenation While Making New Variables

Assuming by 'continuous' you mean more than one contiguous month (ie 2 or more adjacent '1's, separated by one or more non-payment months) then there can be at most 4 periods in a 12 month timeframe.  If your criterion is stricter, say 3 or more, then there can be at most only 3 periods.

Richard

Respected Advisor
Posts: 4,919

Re: Date Concatenation While Making New Variables

You can do it this way:

data have;

input ID   YEAR   ENRIND1-ENRIND12;

datalines;

101 2008    0 0 1 1 1 1 1 1 1 1 1 1

101 2009    1 1 1 1 1 0 0 1 1 1 1 1 

101 2010    1 1 1 1 1 1 1 1 1 0 0 0

101 2011    0 0 1 1 1 1 1 1 1 1 . .

;

proc sort data=have; by id year; run;

data long;

array enr{*} ENRIND1-ENRIND12;

prevEnr = 0;

episode = 0;

do until (last.id);

  set have; by id;

  do m = 1 to 12;

    if not prevEnr and enr{m} then do;

      episode + 1;

      _name_ = cats("First", episode);

      dte = mdy(m, 1, year);

      output;

      end;

    if prevEnr and not coalesce(enr{m}, 0) then do;

      _name_ = cats("Last", episode);

      dte = intnx('MONTH', mdy(m, 1, year), -1, "END");

      output;

      end;

    prevEnr = coalesce(enr{m}, 0);

    end;

  end;

if episode > 0 and prevEnr then do;

  _name_ = cats("Last", episode);

  dte = mdy(12, 31, year);

  output;

  end;

keep id _name_ dte;

format dte mmddyy10.;

run;

proc transpose data=long out=want(drop=_name_);

by id;

var dte;

id _name_;

run;

It will create as many episodes as required.

PG

Message was edited by: PG - Added code for proper closure of last episode and missing statuses at the end of last year.

PG
Contributor
Posts: 20

Re: Date Concatenation While Making New Variables

This one actually got closer to what I had requested the output to look like. But, it turns out I did not need my intervals to be 'wide', just 'long' as Tom's code provides. Thank you for your response.

Solution
‎03-21-2014 10:41 PM
Super User
Super User
Posts: 7,039

Re: Date Concatenation While Making New Variables

Either make it totally vertical and scan for blocks. Or create one long string with 12 characters for each year and use FINDW() to scan across it.

Here is an example using the vertical structure.  (Note to improve performance and save space you could make the VERTICIAL dataset a view.)

data have ;

  input ID   YEAR   ENRIND1-ENRIND12 ;

cards;

101 2008    0 0 1 1 1 1 1 1 1 1 1 1

101 2009    1 1 1 1 1 0 0 1 1 1 1 1

101 2010    1 1 1 1 1 1 1 1 1 0 0 0

run;

data vertical ;

  set have ;

  by id year;

  array m enrind1-enrind12 ;

  do month=1 to 12 ;

    coverage = m(month);

    output;

  end;

run;

data blocks ;

  do until (last.coverage);

    set vertical ;

    by id coverage notsorted ;

    if first.id then interval=0;

    if first.coverage then do;

       interval + 1;

       start = mdy(month,1,year);

    end;

    if last.coverage then do;

       end = intnx('month',mdy(month,1,year),0,'E') ;

       months = intck('month',start,end)+1;

       output;

    end;

  end;

  keep id start end coverage interval months;

  format start end date9.;

run;

proc print;

run;

Obs     ID    coverage    interval        start          end    months

1     101        0           1       01JAN2008    29FEB2008       2

2     101        1           2       01MAR2008    31MAY2009      15

3     101        0           3       01JUN2009    31JUL2009       2

4     101        1           4       01AUG2009    30SEP2010      14

5     101        0           5       01OCT2010    31DEC2010       3

Contributor
Posts: 20

Re: Date Concatenation While Making New Variables

Tom, I forgot to thank you for this. I have been using this quite a bit. It works perfectly for my needs. I only wish I could repay you in some way. Cheers.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 350 views
  • 5 likes
  • 4 in conversation