problem with dates

[Dany1]

Hi!

I have got a problem with dates and I do not know how to solve it!

I have got these two columns for example:

date1                                   date2                      value

05/10/2016                         06/10/2016               3

10/10/2016                         13/10/2016               5

I would like to create a column start, a column stop in order to count the days since 5/10/2016 and the relative value:

start                       stop                 value

0                            1                       3

2                            4                       0

5                            9                       5

I really do not know if this is possible!

Thanks for your help

[Amir]

Hi,

I think I understand how the first output observation is calculated, but can you explain how the last two observations are calculated please.

Regards,

Amir.

[Dany1]

I have got these two columns (for example dose of a drug for a subject is the value):

date1                                   date2                      value

05/10/2016                         06/10/2016               3

10/10/2016                         13/10/2016               5

05/10/2016 is day 0

06/10/2016 is day 1

07/10/2016 is day 2

08/10/2016 is day 3

09/10/2016 is day 4

10/10/2016 is day 5

and so on..

and between day 2 and day 4 the value is 0 so I would like to obtain for the subject an output like this:

start                       stop                 value

0                            1                       3

2                            4                       0

5                            9                       5

Thanks!!

[Kurt_Bremser]

See this:

data have;
input date1 :ddmmyy10. date2 :ddmmyy10. value;
format date1 date2 ddmmyy10.;
cards;
05/10/2016 06/10/2016 3
10/10/2016 13/10/2016 5
;
run;

data want (keep=start stop value);
set have (rename=(value=_value));
retain startpoint;
old_d2 = lag(date2);
if _n_ = 1
then startpoint = date1;
else if old_d2 < date1
then do;
  start = old_d2 - startpoint + 1;
  stop = date1 - startpoint - 1;
  value = 0;
  output;
end;
start = date1 - startpoint;
stop = date2 - startpoint;
value = _value;
output;
run;

proc print data=want noobs;
run;

Result:

start    stop    value

  0        1       3  
  2        4       0  
  5        8       5  

Your stop value for the 3rd observation seems to be wrong, as 13 - 5 gives 8.

[Kurt_Bremser]

I think the second observation in the output is derived from lag(date2) and date1 while the second observation of the input is processed.

[RW9]

You do not need start stops, dates are number of days since the cuttoff, so subtracting the earliest from the latest leaves the number of days between the two.  If you want another way, then the intck() function can count intervals of days, months, quarters etc. between two dates.  And intnx() can increment dates.

[Dany1]

I need the data in that format because I have to compute the time dependent cox regression but I have got this variable dose that change in the lifetime. Thanks for you answer

[gamotte]

Hello,

data have;
    informat date1 date2 ddmmyy10.;
    input date1 date2 value;

    start=intck('day','05oct2016'd, date1);
    stop=intck('day','05oct2016'd, date2);

    cards;
05/10/2016 06/10/2016 3
10/10/2016 13/10/2016 5
    ;
run;

data want;
    merge  have have(where=(nextstart>0) keep=start rename=(start=nextstart));

    lstop=sum(lag(stop),0);

    output;

    if nextstart ne . and stop ne nextstart-1 then do;
        start=lstop+1;
        stop=nextstart-1;
        value=0;
        output;
    end;

    drop nextstart lstop;
run;

[Dany1]

Thank you!!!!!

[Amir · Accepted solution]

Hi,

If you think you have received the solution you require then please mark it as accepted.

Thanks,

Amir.

[RW9]

Its a good idea to mark the actual answer to the question as the Correct Answer.