topic Using a DO Loop to create new date with first. last. logic in SAS Programming

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

kbinan — Fri, 28 Feb 2020 01:20:08 GMT

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;

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

novinosrin — Fri, 28 Feb 2020 01:31:33 GMT



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;

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

kbinan — Fri, 28 Feb 2020 01:40:37 GMT

@novinosrin

THANK YOU SO MUCH! That worked perfectly!

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

novinosrin — Fri, 28 Feb 2020 01:44:09 GMT

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;