DATA Step, Macro, Functions and more

How to flag continous month?

Reply
New Contributor
Posts: 4

How to flag continous month?

 

I have a raw data and not sure how to flag if service_from_date is not a continuous month.

 

for example, from observation1-3, need flag=1; from observation 4-5, need flag=2; from observation 6-13, need flag=3.

observation 1-5 has a same provider, but there is a gap (not continuous) between observation 3 and 4.

 

MEMBER_IDSERVICE_FROM_DATESERVICE_THROUGH_DATESERVICE_PROVIDER_ID
ABC01/05/201701/05/201764058469
ABC02/24/201702/24/201764058469
ABC03/08/201703/08/201764058469
ABC07/10/201707/10/201764058469
ABC08/21/201708/21/201764058469
ABC03/25/201703/25/201764089900
ABC04/30/201704/30/201764089900
ABC05/28/201705/28/201764089900
ABC06/25/201706/25/201764089900
ABC07/23/201707/23/201764089900
ABC08/20/201708/20/201764089900
ABC09/24/201709/24/201764089900
ABC10/22/201710/22/201764089900

 

wanted results:

 

MEMBER_IDSTART_DTEND__DTSERVICE_PROVIDER_ID
ABC01/05/201703/08/201764058469
ABC07/10/201708/21/201764058469
ABC03/25/201710/22/201764089900
Super User
Posts: 12,994

Re: How to flag continous month?

is your definition of "continuous" that the month of service_from_date is exactly one month after the service_through_date from the previous record when the member_id and service_provider_id are the same?

 

Since your results do not show any flag values it looks like you want to collapse the from and to period, correct?

PROC Star
Posts: 1,282

Re: How to flag continous month?

 

data have;
input MEMBER_ID $ SERVICE_FROM_DATE :mmddyy10. SERVICE_THROUGH_DATE :mmddyy10. SERVICE_PROVIDER_ID $;
format SERVICE_FROM_DATE SERVICE_THROUGH_DATE mmddyy10.;
datalines;
ABC 01/05/2017 01/05/2017 64058469
ABC 02/24/2017 02/24/2017 64058469
ABC 03/08/2017 03/08/2017 64058469
ABC 07/10/2017 07/10/2017 64058469
ABC 08/21/2017 08/21/2017 64058469
ABC 03/25/2017 03/25/2017 64089900
ABC 04/30/2017 04/30/2017 64089900
ABC 05/28/2017 05/28/2017 64089900
ABC 06/25/2017 06/25/2017 64089900
ABC 07/23/2017 07/23/2017 64089900
ABC 08/20/2017 08/20/2017 64089900
ABC 09/24/2017 09/24/2017 64089900
ABC 10/22/2017 10/22/2017 64089900
;

 


data want;
do n=1 by 1 until(last.member_id);
set have;
by MEMBER_ID SERVICE_FROM_DATE notsorted;
if n=1 then _start_DATE=SERVICE_FROM_DATE;
array temp(100);
array _temp2(100);
k=month(SERVICE_FROM_DATE);
retain grp 1;
k1=dif(k);
if k1 not in (1,.) then
do;
grp+1;
temp(n-1)=_start_DATE;
_start_DATE=SERVICE_FROM_DATE;
_temp2(n)=n-1;
end;
*output;
end;
do n1= 1 by 1 until(last.member_id);
set have;
by MEMBER_ID SERVICE_FROM_DATE notsorted;
if n1 in _temp2 then
do;
start_date=temp(n1);
output;
end;
end;
if last.member_id then do;
start_date=_start_DATE;
output;
end;

format start_date _start_date mmddyy10.;
drop n temp: _temp: k: grp n:;
run;

 

sorry for lazy and unclean code, but you can. i'll see if i can clean after lunch. I am hungry

PROC Star
Posts: 1,282

Re: How to flag continous month?

Posted in reply to novinosrin

Better version:

NOTES: The code may require tweaking a bit as the service provider changes for the same member id. I trust if you are capable of maintaining this code, you certainly can tweak on your own. Nevertheless, should you face any discrepancies while testing, let me know. And of course, I am sure some super user will give a far better solution than this

 

data have;

input MEMBER_ID $     SERVICE_FROM_DATE :mmddyy10.     SERVICE_THROUGH_DATE :mmddyy10.      SERVICE_PROVIDER_ID $;

format SERVICE_FROM_DATE SERVICE_THROUGH_DATE mmddyy10.;

datalines;

ABC  01/05/2017 01/05/2017 64058469

ABC  02/24/2017 02/24/2017 64058469

ABC  03/08/2017 03/08/2017 64058469

ABC  07/10/2017 07/10/2017 64058469

ABC  08/21/2017 08/21/2017 64058469

ABC  03/25/2017 03/25/2017 64089900

ABC  04/30/2017 04/30/2017 64089900

ABC  05/28/2017 05/28/2017 64089900

ABC  06/25/2017 06/25/2017 64089900

ABC  07/23/2017 07/23/2017 64089900

ABC  08/20/2017 08/20/2017 64089900

ABC  09/24/2017 09/24/2017 64089900

ABC  10/22/2017 10/22/2017 64089900

;

 

 

data want(rename=(SERVICE_THROUGH_DATE=end_date));

retain MEMBER_ID start_date;

     do _n=1 by 1  until(last.member_id);

           set have;

           by MEMBER_ID SERVICE_FROM_DATE notsorted;

           if _n=1 then _start_DATE=SERVICE_FROM_DATE;

           array temp(100) _temporary_;

           array _temp2(100) _temporary_;

           _k=month(SERVICE_FROM_DATE);

           retain _grp 1;

           _k1=dif(_k);

           if _k1 not in (1,.) then

                do;

                     _grp+1;

                     temp(_n-1)=_start_DATE;

                     _start_DATE=SERVICE_FROM_DATE;

                     _temp2(_n)=_n-1;

                end;

          

     end;

     do _n_= 1 by 1 until(last.member_id);

           set have;

           by MEMBER_ID SERVICE_FROM_DATE notsorted;

           if _n_ in _temp2  then

                do;

                     start_date=temp(_n_);

                     output;

                end;

           if last.member_id then

                do;

                     start_date=_start_DATE;

                     output;

                end;

     end; 

format start_date mmddyy10.;

drop _: SERVICE_FROM_DATE;

run;

 

 

 

 

 

 

PROC Star
Posts: 1,190

Re: How to flag continous month?

Posted in reply to novinosrin

@novinosrin "I am hungry".. gotta love it Smiley Very Happy

PROC Star
Posts: 1,282

Re: How to flag continous month?

@draycut Yep indeed loved it . I can't miss the amazing subway's sub of the day oven roasted chicken topping with mayonnaise and hot sauce lol

PROC Star
Posts: 1,190

Re: How to flag continous month?

[ Edited ]

If you have SAS/ETS do something like this, since the creation of START_DT and END_DT really only depends on looking 1 obs back and 1 ahead Smiley Happy

 

 

proc expand data=have out=have_ll method=none;
   by SERVICE_PROVIDER_ID;
   id SERVICE_FROM_DATE;
   convert SERVICE_FROM_DATE=lead_SERVICE_FROM_DATE / transformout=(lead 1);
   convert SERVICE_FROM_DATE=lag_SERVICE_FROM_DATE / transformout=(lag 1);
run;
   
data want(keep=MEMBER_ID START_DT END_DT SERVICE_PROVIDER_ID);
   format MEMBER_ID START_DT END_DT SERVICE_PROVIDER_ID;
   set have_ll;
   by SERVICE_PROVIDER_ID;
   monthback=month(SERVICE_FROM_DATE)-month(lag_SERVICE_FROM_DATE);
   monthforward=month(lead_SERVICE_FROM_DATE)-month(SERVICE_FROM_DATE);

   if first.SERVICE_PROVIDER_ID then START_DT=SERVICE_FROM_DATE;
   else if (monthback=1 and monthforward>1) or last.SERVICE_PROVIDER_ID then do;
      END_DT=SERVICE_THROUGH_DATE;
      output;
      START_DT=lead_SERVICE_FROM_DATE;
   end;

   retain START_DT;format START_DT END_DT mmddyy10.;
run;

 

 

PROC Star
Posts: 8,092

Re: How to flag continous month?

I think that the following meets your specifications:

data want (keep=MEMBER_ID START_DT END_DT SERVICE_PROVIDER_ID);
  set have;
  format START_DT END_DT mmddyy10.;
  by SERVICE_PROVIDER_ID;
  retain START_DT in_process;
  set have ( firstobs = 2 keep = SERVICE_FROM_DATE rename = (SERVICE_FROM_DATE = Next_Date) )
      have (      obs = 1 drop = _all_);
  if first.SERVICE_PROVIDER_ID or in_process eq 0 then do;
    in_process=1;
    START_DT=SERVICE_FROM_DATE;
  end;
  if last.SERVICE_PROVIDER_ID or intck('month',SERVICE_FROM_DATE,Next_Date) gt 1 then do;
    END_DT=SERVICE_THROUGH_DATE;
    output;
    in_process=0;
  end;
run;

Art, CEO, AnalystFinder.com

 

PROC Star
Posts: 2,211

Re: How to flag continous month?

Like this?

data WANT;
  retain START_DT;
  set HAVE end=LASTOBS;
  PREV_ST   =lag(SERVICE_FROM_DATE);
  PREV_END  =lag(SERVICE_THROUGH_DATE);
  PREV_PROV =lag(SERVICE_PROVIDER_ID);
  if _N_=1 then START_DT=SERVICE_FROM_DATE;
  if _N_>1;
  if   (month(PREV_ST) + 1 ne month(SERVICE_FROM_DATE) )
    or (      PREV_PROV    ne   SERVICE_PROVIDER_ID    )
    or LASTOBS then do;
    END_DT     = ifn(LASTOBS, SERVICE_THROUGH_DATE,PREV_END);
    PROVIDER_ID= ifn(LASTOBS, SERVICE_PROVIDER_ID ,PREV_PROV);
    output; 
    START_DT   = SERVICE_FROM_DATE;
  end;
  format  	START_DT	END_DT	date9.;
  keep MEMBER_ID	START_DT	END_DT	PROVIDER_ID;
run;

 

MEMBER_ID START_DT END_DT PROVIDER_ID
ABC 05JAN2017 08MAR2017 64058469
ABC 10JUL2017 21AUG2017 64058469
ABC 25MAR2017 22OCT2017 64089900

 

 

 

PROC Star
Posts: 8,092

Re: How to flag continous month?

@ChrisNZ: Using lag or reading the next record (like I did) accomplish the same thing. However, since you didn't use the INTCK function, your code will fail if the visits go across years. e.g.:

data have;
  input MEMBER_ID $ SERVICE_FROM_DATE :mmddyy10. SERVICE_THROUGH_DATE :mmddyy10. SERVICE_PROVIDER_ID $;
  format SERVICE_FROM_DATE SERVICE_THROUGH_DATE mmddyy10.;
  datalines;
ABC 01/05/2017 01/05/2017 64058469
ABC 02/24/2017 02/24/2017 64058469
ABC 03/08/2017 03/08/2017 64058469
ABC 07/10/2017 07/10/2017 64058469
ABC 08/21/2017 08/21/2017 64058469
ABC 03/25/2017 03/25/2017 64089900
ABC 04/30/2017 04/30/2017 64089900
ABC 05/28/2017 05/28/2017 64089900
ABC 06/25/2017 06/25/2017 64089900
ABC 07/23/2017 07/23/2017 64089900
ABC 08/20/2017 08/20/2017 64089900
ABC 09/24/2017 09/24/2017 64089900
ABC 10/22/2017 10/22/2017 64089900
ABC 11/22/2017 11/22/2017 64089900
ABC 12/22/2017 12/22/2017 64089900
ABC 01/22/2018 01/22/2018 64089900
ABC 02/22/2018 02/22/2018 64089900
;

Art, CEO, AnalystFinder.com

 

PROC Star
Posts: 2,211

Re: How to flag continous month?

True. I just wanted to supply a much shorter data step than the 2 above, which were the only 2 when I started replying..

Ask a Question
Discussion stats
  • 10 replies
  • 234 views
  • 5 likes
  • 6 in conversation