Collapsing and updating data

Reply
New Contributor
Posts: 3

Collapsing and updating data


Hello,

I have the following data;

member       eff_dt              end_dt            flag      coverage

123456       2011-01-01       9999-12-31       N      2011-10-31

123456       2011-01-01       9999-12-31       N      2011-11-30

123456       2011-01-01       9999-12-31       N      2011-12-31

123456       2011-01-01       9999-12-31       N      2012-01-31

123456       2011-01-01       9999-12-31       N      2012-02-29

123456       2011-01-01       9999-12-31       N      2012-03-31

123456       2011-01-01       9999-12-31       N      2012-04-30

123456       2011-01-01       9999-12-31       N      2012-05-31

123456       2011-01-01       9999-12-31       N      2012-06-30

123456       2011-01-01       9999-12-31       N      2012-07-31

123456       2011-01-01       9999-12-31       N      2012-08-31

123456       2011-01-01       9999-12-31       Y      2012-09-30

123456       2011-01-01       9999-12-31       Y      2012-10-31

123456       2011-01-01       9999-12-31       Y      2012-11-30

123456       2011-01-01       9999-12-31       Y      2012-12-31

123456       2011-01-01       9999-12-31       Y      2013-01-31

123456       2011-01-01       9999-12-31       Y      2013-02-28

123456       2011-01-01       9999-12-31       Y      2013-03-31

123456       2011-01-01       9999-12-31       N      2013-04-30

123456       2011-01-01       9999-12-31       N      2013-05-31

123456       2011-01-01       9999-12-31       N      2013-06-30

123456       2011-01-01       9999-12-31       Y      2013-07-31

123456       2011-01-01       9999-12-31       Y      2013-08-31

123456       2011-01-01       9999-12-31       Y      2013-09-30

123456       2011-01-01       9999-12-31       Y      2013-10-31

123456       2011-01-01       9999-12-31       N      2013-11-30

123456       2011-01-01       9999-12-31       N      2013-12-31

123456       2011-01-01       9999-12-31       Y      2014-01-31

123456       2011-01-01       9999-12-31       Y      2014-02-28

123456       2011-01-01       9999-12-31       Y      2014-03-31

123456       2011-01-01       9999-12-31       Y      2014-04-30

Basically, I would like to collapse it and make it look like this:

member  eff_dt            end_dt            flag

123456  10/01/2011      08/31/2012      N

123456  09/01/2012      03/31/2013      Y

123456  04/01/2013      06/30/2013      N

123456  07/01/2013      10/31/2013      Y

123456  11/01/2013      12/31/2013      N

123456  01/01/2013      04/30/2014      Y

Any ideas? Any help/suggestion is appreciated.

Thanks!

Grand Advisor
Posts: 10,196

Re: Collapsing and updating data

Since your example data shows not eff_dt that match the desired outcome and there is no rule on where the end_dt comes from and what rule may be used for selecting the flag value you need to provide a lot more information.

New Contributor
Posts: 3

Re: Collapsing and updating data

Basically, the eff_dt and end_dt is replaced by the coverage date. The eff_dt becomes the first of the month on where the flag change and the end_dt is the last day of the month before the flag changes.

Frequent Contributor
Posts: 81

Re: Collapsing and updating data

This is close. check eff_dt in line 6.  Our answers are different.

data new;

input @1 member $8.     @15 eff_dt yymmdd10.

       @31 end_dt yymmdd10.  @48 flag $2. @55 coverage yymmdd10.;

  format eff_dt end_dt coverage mmddyy10.;

cards;

123456       2011-01-01       9999-12-31       N      2011-10-31

123456       2011-01-01       9999-12-31       N      2011-11-30

123456       2011-01-01       9999-12-31       N      2011-12-31

123456       2011-01-01       9999-12-31       N      2012-01-31

123456       2011-01-01       9999-12-31       N      2012-02-29

123456       2011-01-01       9999-12-31       N      2012-03-31

123456       2011-01-01       9999-12-31       N      2012-04-30

123456       2011-01-01       9999-12-31       N      2012-05-31

123456       2011-01-01       9999-12-31       N      2012-06-30

123456       2011-01-01       9999-12-31       N      2012-07-31

123456       2011-01-01       9999-12-31       N      2012-08-31

123456       2011-01-01       9999-12-31       Y      2012-09-30

123456       2011-01-01       9999-12-31       Y      2012-10-31

123456       2011-01-01       9999-12-31       Y      2012-11-30

123456       2011-01-01       9999-12-31       Y      2012-12-31

123456       2011-01-01       9999-12-31       Y      2013-01-31

123456       2011-01-01       9999-12-31       Y      2013-02-28

123456       2011-01-01       9999-12-31       Y      2013-03-31

123456       2011-01-01       9999-12-31       N      2013-04-30

123456       2011-01-01       9999-12-31       N      2013-05-31

123456       2011-01-01       9999-12-31       N      2013-06-30

123456       2011-01-01       9999-12-31       Y      2013-07-31

123456       2011-01-01       9999-12-31       Y      2013-08-31

123456       2011-01-01       9999-12-31       Y      2013-09-30

123456       2011-01-01       9999-12-31       Y      2013-10-31

123456       2011-01-01       9999-12-31       N      2013-11-30

123456       2011-01-01       9999-12-31       N      2013-12-31

123456       2011-01-01       9999-12-31       Y      2014-01-31

123456       2011-01-01       9999-12-31       Y      2014-02-28

123456       2011-01-01       9999-12-31       Y      2014-03-31

123456       2011-01-01       9999-12-31       Y      2014-04-30

  ;

proc print;  run;

data new2;  set new;  by flag notsorted;

    retain hold_dt 0;

    if first.flag then do;

       hold_dt=intnx('month',coverage,0);

     end;

     if last.flag then do;

        eff_dt=hold_dt;   end_dt=coverage; 

      output;  drop hold_dt coverage;

     end;

proc print;  run;


 

112345610/01/201108/31/2012N
212345609/01/201203/31/2013Y
312345604/01/201306/30/2013N
412345607/01/201310/31/2013Y
512345611/01/201312/31/2013N
612345601/01/201404/30/2014Y

Jim

New Contributor
Posts: 3

Re: Collapsing and updating data

Thank you Jim. You've just given me enough information to tackle this on my own. Thanks again!

Respected Advisor
Posts: 4,955

Re: Collapsing and updating data

Here's one way, assuming I understand what you are trying to do.

proc summary data=have;

  by member flag notsorted;

  var coverage;

  output out=almost (keep=member flag eff_dt end_dt) min(coverage)=eff_dt max(coverage)=end_dt;

run;

data want;

  set almost;

  eff_dt = intnx('month', eff_dt, 0);

run;

It assumes that COVERAGE is an actual SAS date, not a character string.  It's just a different approach, and Jim's should work equally well (assuming that MEMBER gets added to the BY statement, since you probably have many members in the actual data).

Good luck.

Ask a Question
Discussion stats
  • 5 replies
  • 199 views
  • 3 likes
  • 4 in conversation