DATA Step, Macro, Functions and more

Calculate Time from Conditional by ID

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Calculate Time from Conditional by ID

Hello I would like to calculate the time post-treatment but I have pre-treatment data and sometimes there are collection dates before or inbetween pre/post treatment flag. I want to have days post treatment with day 0 being the post date flag. 

 

 

data have;
input ID $ NO Treatment $ @11 DATE MMDDYY10.;
FORMAT DATE DATE9.;
cards;
1 44 Pre 10/21/2015
1 44 _ 10/22/2015
1 28 Post 10/23/2015
1 22 _ 11/23/2015
2 35 Pre 9/21/2015
2 35 Post 10/21/2015
2 35 _ 10/26/2015
2 35 _ 10/31/2015
3 35 _ 5/11/2015
3 35 Pre 5/16/2015
3 35 Post 6/22/2015
3 35 Post 6/27/2015
;;;;
run;


data want;
input ID $ NO Treatment $ @11 DATE MMDDYY10. TIME;
FORMAT DATE DATE9.;
cards;
1 44 Pre 10/21/2015 .
1 44 _ 10/22/2015 .
1 28 Post 10/23/2015 0
1 22 _ 11/23/2015 29
2 35 Pre 9/21/2015 .
2 35 Post 10/21/2015 0
2 35 _ 10/26/2015 5
2 35 _ 10/31/2015 10
3 35 _ 5/11/2015 .
3 35 Pre 5/16/2015 .
3 35 Post 6/22/2015 0
3 35 Post 6/27/2015 5
;;;;
run;

 

Thank you


Accepted Solutions
Solution
‎03-27-2017 05:03 PM
Super User
Posts: 10,497

Re: Calculate Time from Conditional by ID

This almost works barring a description of why the last POST doesn't reset the counter to 0.

 

data want;
   set have;
   by id date;
   retain postdate postflag;
   if first.id then do;
      postdate=.; /* date of the most recent Post record*/
      postflag=0; /* flag for has the "post" been found*/
   end;
   if treatment = 'Pre' then do;
      postflag=0;
      postdate=.;
   end;

   if treatment = 'Post' then do;
      postflag=1;
      postdate=date;
   end;
   time = date-postdate;
   drop postdate postflag;
run;

View solution in original post


All Replies
Super User
Posts: 10,497

Re: Calculate Time from Conditional by ID

[ Edited ]

Is

1 22 _ 11/23/2015

supposed to be

1 28 _ 11/23/2015

 

If not what role may the NO variable play in the logic of assignment, if any?

 

Also what is the rule involved for that last pair

3 35 Post 6/22/2015 0
3 35 Post 6/27/2015 5

where  the second has a 5 instead of 0??? it is a Post date and would expect it to be 0.

 

And post example code into CODE boxes opened with the forum {i} icon. Your example data sets will not run as the forum reformatted things so that the date does not always start at column 11.

This does work:

data have;
   informat id $4. no best4. treatment $4. date mmddyy10.;
   input ID  NO Treatment  DATE;
   FORMAT DATE DATE9.;
cards;
1 44 Pre 10/21/2015
1 44 _ 10/22/2015
1 28 Post 10/23/2015
1 22 _ 11/23/2015
2 35 Pre 9/21/2015
2 35 Post 10/21/2015
2 35 _ 10/26/2015
2 35 _ 10/31/2015
3 35 _ 5/11/2015
3 35 Pre 5/16/2015
3 35 Post 6/22/2015
3 35 Post 6/27/2015
;;;;
run;

 

And Oct 23 to Nov 23 will be 31 days not 29.

Occasional Contributor
Posts: 7

Re: Calculate Time from Conditional by ID

My apologies, I just left that variable in there from a different datalines statement. It has no significance.

Solution
‎03-27-2017 05:03 PM
Super User
Posts: 10,497

Re: Calculate Time from Conditional by ID

This almost works barring a description of why the last POST doesn't reset the counter to 0.

 

data want;
   set have;
   by id date;
   retain postdate postflag;
   if first.id then do;
      postdate=.; /* date of the most recent Post record*/
      postflag=0; /* flag for has the "post" been found*/
   end;
   if treatment = 'Pre' then do;
      postflag=0;
      postdate=.;
   end;

   if treatment = 'Post' then do;
      postflag=1;
      postdate=date;
   end;
   time = date-postdate;
   drop postdate postflag;
run;
Occasional Contributor
Posts: 7

Re: Calculate Time from Conditional by ID

Works like a charm! 

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 122 views
  • 0 likes
  • 2 in conversation