Continuous Eligibility over a time period at 2 different levels.

Reply
Contributor
Posts: 21

Continuous Eligibility over a time period at 2 different levels.

I put this macro together from some other code I'd found for finding where a single date fell within a begin-end date pair and other eligibility related code.  I needed to find whether a series of eligibility ranges qualified as continuous (or contiguous), gave me the days eligible and a high-level flag for whatever key is used (member id, subscriber id, whatever).

 

So this can be called to examine an input dataset, figure out the total continuous state for the 'id' as well as generate a detail table that has the begin & end date pairs, days between them and continuous flag.  Maybe this will be helpful to others.

 

 

%macro continuous(id=,begin=,end=,indata=,outid=,outsegs=);

proc sql;
create table work.cont1 as
select distinct &id as id,&begin as begin format yymmdd10.,&end as end format yymmdd10.
from &indata
where &begin is not missing and &end is not missing
order by &id ,&begin ,&end ;

data work.cont2;
  set work.cont1;
  by id begin;
  retain rbegin rend breakday;
  drop rbegin rend;

  if first.id then do;
    rbegin=begin;
    rend=end;
    breakday=0;
  end;

  else do;
    if rbegin<=begin <=rend +1 then do;
      breakday=0;
      if end > rend then rend=end;
    end;

    else if begin>rend+1 then do;
      breakday=begin-rend-1;
      rbegin=begin;
      rend=end;
    end;

  end;
run;

proc sql;
    create table &outsegs
    as
    select *
        ,DATDIF(begin, end, 'actual') as days
        ,CASE
            WHEN breakday = 0 and begin is not missing and end is not missing
                THEN 'Y'
                ELSE 'N'
            END AS Continuous_Elig_Segment
     from work.cont2
;    

    create table &outid
    as
    select id as &id
        ,min(CASE
            WHEN breakday = 0 and begin is not missing and end is not missing
                THEN begin
            END) AS Continuous_Elig_Begin format date9.
        ,max(CASE
            WHEN breakday = 0 and begin is not missing and end is not missing
                THEN end
            END) AS Continuous_Elig_End format date9.
        ,min(CASE
            WHEN breakday = 0 and begin is not missing and end is not missing
                THEN 'Y'
                ELSE 'N'
            END) AS Continuous_Elig_Segment
        ,DATDIF(calculated Continuous_Elig_Begin, calculated Continuous_Elig_End, 'actual') as days    
     from work.cont2
    group by id
;
quit;

%mend continuous;

Ask a Question
Discussion stats
  • 0 replies
  • 282 views
  • 0 likes
  • 1 in conversation