topic Re: how to code for continous enrollment using LAG and RETAIN function in SAS Programming

how to code for continous enrollment using LAG and RETAIN function

manya92 — Thu, 03 May 2018 17:45:35 GMT

Hi all,

I need to create a continous enrollment dataset from a dataset which has enrollment for all months across 2011-2017. The dataset I have looks like this :

enrolid dtstart dtend

420 07/02/2011 07/31/2011

420 08/01/2011 08/31/2011

420 09/01/2011 09/30/2011

500 07/02/2011 07/31/2011

I only want a dataset looking like this:

enrolid period_start period_stop

420 07/01/2011 09/30/2011

How can i do this using retain and lag functions ?

There should be no gaps in the dates.. they should be continous

I am using Batch SAS

Re: how to code for continous enrollment using LAG and RETAIN function

novinosrin — Thu, 03 May 2018 17:51:28 GMT

data have;
input enrolid  dtstart   :mmddyy10.             dtend   :mmddyy10.  ;
format dtstart             dtend   mmddyy10.  ;
cards;
420       07/02/2011       07/31/2011
420        08/01/2011     08/31/2011
420       09/01/2011        09/30/2011
500       07/02/2011         07/31/2011
;
 

proc sql;
create table want as
select enrolid, min(dtstart) as period_start   format= mmddyy10. ,max(dtend) as period_stop  format= mmddyy10.
from have
group by enrolid;
quit;

Re: how to code for continous enrollment using LAG and RETAIN function

manya92 — Thu, 03 May 2018 17:51:23 GMT

I am new to sql could you show how to do this using SAS retain and lag functions ?

Re: how to code for continous enrollment using LAG and RETAIN function

Astounding — Thu, 03 May 2018 17:54:10 GMT

Are you guaranteeing that there are no gaps in the dates?

If there are gaps in the dates would you want a second observation for the same ID?

Re: how to code for continous enrollment using LAG and RETAIN function

novinosrin — Thu, 03 May 2018 17:56:05 GMT

data want;
set have;
by enrolid;
retain _t;
if first.enrolid then _t=dtstart;
if last.enrolid then do;period_start=_t;
period_stop=dtend;output;end;
format period_start period_stop mmddyy10.;
drop dt: _:;
run;

Note: This assumes your dataset is sorted

Re: how to code for continous enrollment using LAG and RETAIN function

PaigeMiller — Thu, 03 May 2018 18:00:34 GMT

I don't see a need for RETAIN here.

Assumes data is sorted by ENROLID and DTSTART.

*** UNTESTED CODE ***

data want;
    set have;
    by enrolid;
    prev_dtend=lag(dtend);
    if dtstart ^= prev_dtend+1 then continuous_dates+1;
run;
proc summary nway data=want;
    class enrolid continuous_dates;
    var dtstart dtend;
    output out=want2 min(dtstart)=period_start max(dtend)=period_stop;
run;

Re: how to code for continous enrollment using LAG and RETAIN function

mkeintz — Thu, 03 May 2018 18:05:23 GMT

You don't need a lag for this task. But retain is useful:

data want;
  set have  (rename=(dtend=dtend_last));
  by enrolid;
  if first.enrolid then dtstart_beg=dtstart;
  retain dtstart_beg;
  if last.enrolid;
run;

Re: how to code for continous enrollment using LAG and RETAIN function

manya92 — Thu, 03 May 2018 18:06:34 GMT

There are gaps in the data.

if the gap is more than a day we have another row for the same enrolid..

Re: how to code for continous enrollment using LAG and RETAIN function

PaigeMiller — Thu, 03 May 2018 18:07:04 GMT

This doesn't meet the user requirement

There should be no gaps in the dates.. they should be continous

Re: how to code for continous enrollment using LAG and RETAIN function

mkeintz — Thu, 03 May 2018 18:23:27 GMT

Thank you @PaigeMiller. I wasn't paying sufficient attention.

Now lag helps in detecting start of a new time span.

data want;
  set have;
  by enrolid;

  merge have (           keep=dtend  rename=(dtend=dtend_last))
        have (firstobs=2 keep=dtstart rename=(dtstart=dtstart_next));

  if first.enrolid or dtstart>sum(lag(dtend),1) then dtstart_beg=dtstart;
  retain dtstart_beg;

  if last.enrolid or dtstart_next>dtend+1;
  drop dtstart dtend dtstart_next;

run;

Re: how to code for continous enrollment using LAG and RETAIN function

manya92 — Thu, 03 May 2018 19:08:06 GMT

There are gaps in the dates but whenever they are there we have another row for the enrollment id. gap means more than 1 day of no enrollment

Re: how to code for continous enrollment using LAG and RETAIN function

manya92 — Thu, 03 May 2018 19:38:35 GMT

data want;
  set have  (rename=(dtend=dtend_last));
  by enrolid;
  if first.enrolid then dtstart_beg=dtstart;
  retain dtstart_beg;
  if last.enrolid; 
run;

what do you mean by the line highlighted in red

Re: how to code for continous enrollment using LAG and RETAIN function

manya92 — Thu, 03 May 2018 19:41:24 GMT

and this does not work the same as sql

Re: how to code for continous enrollment using LAG and RETAIN function

manya92 — Thu, 03 May 2018 20:01:34 GMT

what does

dtstart>sum(lag(dtend),1)

mean ?

and also i am not clear what this does ?

if last.enrolid or dtstart_next>dtend+1;

Re: how to code for continous enrollment using LAG and RETAIN function

mkeintz — Fri, 04 May 2018 02:15:10 GMT

@manya92 wrote:

what does
dtstart>sum(lag(dtend),1) 
mean ?

and also i am not clear what this does ?
if last.enrolid or dtstart_next>dtend+1;

dtstart>sum*lag(dtend),1)

is almost identical to

dtstart>lag(dtend)+1

except the latter will generate an error note for the first observation - because lag of anything is missing for the first obs.

The IF statement above is a "subsetting if" - i.e. the data step only keeps cases that satisfy the if expression.

Re: how to code for continous enrollment using LAG and RETAIN function

manya92 — Fri, 04 May 2018 17:17:57 GMT

how can you have the set and merge statements in the same data step ?

Re: how to code for continous enrollment using LAG and RETAIN function

mkeintz — Fri, 04 May 2018 18:34:10 GMT

The SET statement starts one data stream, and the MERGE statement starts another data stream. And they are synchronized streams.

Of course if you have two streams of data from data set HAVE, you'll have any common variable being read once from each stream. But you can't have two values occupy one variable, so I use the KEEP= and RENAME= parameters to avoid such collisions.

The SET statement is used for nothing more than to generate FIRST.ENROLID and LAST.ENROLID variables for easy detection of boundaries for each ID. The MERGE statement has one of the merged sub-streams start at FIRSTOBS=2 so that you can look ahead one observation. This makes it easy to tell if the next observation starts more than one day after the current obs ends.

Re: how to code for continous enrollment using LAG and RETAIN function

mkeintz — Fri, 04 May 2018 18:35:12 GMT

No red line is showing up.