topic retaining values from both row above and row below in SAS Programming

retaining values from both row above and row below

Brander — Mon, 23 Jan 2017 14:42:43 GMT

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!

Re: retaining values from both row above and row below

RW9 — Mon, 23 Jan 2017 14:53:27 GMT

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;

Re: retaining values from both row above and row below

Astounding — Mon, 23 Jan 2017 15:06:22 GMT

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;

Re: retaining values from both row above and row below

Brander — Mon, 23 Jan 2017 15:09:20 GMT

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