retaining values from both row above and row below

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

retaining values from both row above and row below

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:

idstartstopMed
120050101200508011
12005100120051201

1

And what I need is this:

idstartstopMed
120050101200508011
120050801200510010
120051001200512011

 

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!


Accepted Solutions
Solution
‎01-30-2017 03:43 AM
Super User
Super User
Posts: 7,417

Re: retaining values from both row above and row below

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;

View solution in original post


All Replies
Solution
‎01-30-2017 03:43 AM
Super User
Super User
Posts: 7,417

Re: retaining values from both row above and row below

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;

New Contributor
Posts: 2

Re: retaining values from both row above and row below

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

 

Super User
Posts: 5,093

Re: retaining values from both row above and row below

[ Edited ]

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 195 views
  • 4 likes
  • 3 in conversation