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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.