## Calculate Time from Conditional by ID

Solved
Occasional Contributor
Posts: 7

# 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: 13,292

## 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;

All Replies
Super User
Posts: 13,292

## 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: 13,292

## 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.