retaining values from both row above and row below

[Brander]

Hi,

I want to define time periods on and off medication to use as a time varying variable. I have start and stop dates for being ON medication, but need to add rows with start and stop of being OFF medication.

So what I have is something along the lines of the following:

id	start	stop	Med
1	20050101	20050801	1
1	20051001	20051201	1

And what I need is this:

id	start	stop	Med
1	20050101	20050801	1
1	20050801	20051001	0
1	20051001	20051201	1

I've been trying to use the retain function, but am at a loss on how to get the value of the start date on the row BELOW to use as the stop date on a new row.

Any ideas?

Thanks in advance!

[RW9 · Accepted solution]

Always post test data as a datastep, we can't see structure otherwise.

How about:

data have;
  input id	start	stop	Med;
datalines;
1	20050101	20050801	1
1	20051001	20051201	1
;
run;

/* First pass, get start */
data want (drop=lstst);
  set have;
  by id;
  retain lstst;
  if first.id then do;
    start_updated=start;
    end_updated=stop;
    output;
    lstst=stop;
  end;
  else do;
    if start ne lstst then do;
      start_updated=lstst;
      end_updated=start;
      output;
      start_updated=start;
      end_updated=stop;
      output;
    end;
    else output;
    lstst=stop;
  end;
run;

[Brander]

Hi RW9,

Thank you very much for your reply! It really seemed to solve my problem!

I'm sorry for not posting any test data, it's the first time I'm using this forum.

Best,

Gustaf

[Astounding]

It's easier to output the observations in a different order.  You can always re-sort them later if necessary.

proc sort data=have;

by id start;

run;

data want (drop=prior_stop);

set have;

by id;

prior_stop = lag(stop);

output;

if first.id=0 and start > prior_stop;

med=0;

stop = start;

start = prior_stop;

output;

run;