Using a DO Loop to create new date with first. last. logic

[kbinan]

Hello SAS users!

I am trying to get a MIN and MAX date onto my data to enable the  creation of  a single record when I roll this up by CLM_NUM.  What I want is the earliest FST_SRVC_DT and the latest LST_SRVC_DT on all claim lines for a given CLM_NUM.

This is what I want it to look like:

CLM_NUM	FST_SRVC_DT	LST_SRVC_DT	IP_START	IP_END
756653xxXXX	22Dec2018	24Dec2018	12/22/2018	12/31/2018
756653xxXXX	25Dec2018	29Dec2018	12/22/2018	12/31/2018
756653xxXXX	30Dec2018	31Dec2018	12/22/2018	12/31/2018

Data is previously sorted by CLM_NUM - what am I doing wrong?  I end up with only the last record looking like I want it to.

DATA WORK.want;

SET WORK.have;

BY CLM_NUM;

IF FIRST.CLM_NUM

THEN DO;

  IP_START = FST_SRVC_DT;

END;

ELSE IF LAST.CLM_NUM

THEN DO;

   IP_END = LST_SRVC_DT;

END;

RETAIN IP_START IP_END;

format IP_START MMDDYY10.;

format IP_END MMDDYY10.;

IP_START = MIN(IP_START,FST_SRVC_DT);

IP_END = MAX(IP_END,LST_SRVC_DT);

RUN;

[novinosrin · Accepted solution]

data have;
input CLM_NUM :$15.	(FST_SRVC_DT	LST_SRVC_DT) (:date9.);*	IP_START	IP_END ;
format 	FST_SRVC_DT	LST_SRVC_DT date9.;
cards;
756653xxXXX	22Dec2018	24Dec2018	12/22/2018	12/31/2018
756653xxXXX	25Dec2018	29Dec2018	12/22/2018	12/31/2018
756653xxXXX	30Dec2018	31Dec2018	12/22/2018	12/31/2018 
;

/*Datastep*/

data want;
 do _n_=1 by 1 until(last.CLM_NUM);
  set have;
  by CLM_NUM;
  IP_START=min(IP_START, FST_SRVC_DT);
  IP_END=max( IP_END,LST_SRVC_DT);
 end;
 do _n_=1 to _n_;
  set have;
  output;
 end;
 format  IP_START IP_END date9.;
run;

 

[kbinan]

@novinosrin

THANK YOU SO MUCH!  That worked perfectly!

[novinosrin]

On the other hand, Proc SQL autoremerge is very convenient 

data have;
input CLM_NUM :$15.	(FST_SRVC_DT	LST_SRVC_DT) (:date9.);*	IP_START	IP_END ;
format 	FST_SRVC_DT	LST_SRVC_DT date9.;
cards;
756653xxXXX	22Dec2018	24Dec2018	12/22/2018	12/31/2018
756653xxXXX	25Dec2018	29Dec2018	12/22/2018	12/31/2018
756653xxXXX	30Dec2018	31Dec2018	12/22/2018	12/31/2018 
;
/*Proc SQL*/

proc sql;
create table want as
select * ,min( FST_SRVC_DT) as 	IP_START format=date9.,max(	LST_SRVC_DT) as IP_END format=date9.
from have
group by CLM_NUM
order by CLM_NUM, FST_SRVC_DT;
quit;