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;
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;
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;
THANK YOU SO MUCH! That worked perfectly!
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.