topic Re: Keeping Only 1 Observation Which Immediately Follows a Date in a Separate Variable Column in Statistical Procedures

Keeping Only 1 Observation Which Immediately Follows a Date in a Separate Variable Column

ssulli11 — Fri, 23 Nov 2018 00:44:38 GMT

Good evening all!

I am looking to keep only the first observation following a date in a separate variable column. I want to keep only the observation in which the "RxStart" is the observation most immediately following the "firstDay" variable. I truly appreciate any and all help! Thank you!!

Here is an example of what I have:

ID	RxStart	RxEnd	firstDay
1	18141	18171	18018
1	18079	18109	18018
1	17949	18064	18018
1	18079	18094	18018
1	18019	18033	18018
1	18141	18171	18018
2	18079	18109	17957
2	17929	17959	17957
2	17988	18018	17957

And here is what I would want:

ID	RxStart	RxEnd	firstDay
1	18019	18033	18018
2	17988	18018	17957

Re: Keeping Only 1 Observation Which Immediately Follows a Date in a Separate Variable Column

novinosrin — Fri, 23 Nov 2018 01:41:37 GMT





data have;
input ID	RxStart	RxEnd	firstDay;
cards;
1	18141	18171	18018
1	18079	18109	18018
1	17949	18064	18018
1	18079	18094	18018
1	18019	18033	18018
1	18141	18171	18018
2	18079	18109	17957
2	17929	17959	17957
2	17988	18018	17957
;


proc sql;
create table want(drop=m) as
select *, ifn(rxstart-firstday>0,rxstart-firstday,.) as m
from have
group by id
having m= min(m) ;
quit;

Re: Keeping Only 1 Observation Which Immediately Follows a Date in a Separate Variable Column

novinosrin — Fri, 23 Nov 2018 02:00:02 GMT

data want;
do until(last.id);
set have;
by id;
m=ifn(rxstart-firstday>0,rxstart-firstday,.) ;
m1=min(m,m1);
end;
do until(last.id);
set have;
by id;
m=ifn(rxstart-firstday>0,rxstart-firstday,.) ;
if m1=m then output;
end;
drop m:;
run;

Re: Keeping Only 1 Observation Which Immediately Follows a Date in a Separate Variable Column

Kurt_Bremser — Fri, 23 Nov 2018 12:37:19 GMT

Simple approach with sorting and a data step:

data have;
infile cards dlm='09'x;
input ID RxStart RxEnd firstDay;
cards;
1	18141	18171	18018
1	18079	18109	18018
1	17949	18064	18018
1	18079	18094	18018
1	18019	18033	18018
1	18141	18171	18018
2	18079	18109	17957
2	17929	17959	17957
2	17988	18018	17957
;
run;

proc sort data=have;
by id rxstart;
run;

data want;
set have;
by id;
retain flag;
if first.id then flag = 1;
if flag and rxstart >= firstday
then do;
  output;
  flag = 0;
end;
drop flag;
run;

proc print data=want noobs;
run;

Result:

        Rx              first
ID    Start    RxEnd     Day

 1    18019    18033    18018
 2    17988    18018    17957

Note how presenting example data in a data step makes it easy to recreate for testing (just copy/paste & submit).

Re: Keeping Only 1 Observation Which Immediately Follows a Date in a Separate Variable Column

ssulli11 — Fri, 23 Nov 2018 20:29:32 GMT

Thank you so much for both solutions!! This worked like a charm. Appreciate your time and help.