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
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;
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.
My apologies, I just left that variable in there from a different datalines statement. It has no significance.
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;
Works like a charm!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.