topic Re: Calculate Time from Conditional by ID in SAS Programming

Calculate Time from Conditional by ID

jckbnimble — Mon, 27 Mar 2017 19:22:58 GMT

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

Re: Calculate Time from Conditional by ID

ballardw — Mon, 27 Mar 2017 20:29:12 GMT

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.

Re: Calculate Time from Conditional by ID

jckbnimble — Mon, 27 Mar 2017 20:24:45 GMT

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

Re: Calculate Time from Conditional by ID

ballardw — Mon, 27 Mar 2017 20:31:35 GMT

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;

Re: Calculate Time from Conditional by ID

jckbnimble — Mon, 27 Mar 2017 21:03:28 GMT

Works like a charm!