BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Brander
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
PROC Star

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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