topic problem with dates in SAS Programming

problem with dates

Dany1 — Thu, 06 Sep 2018 09:51:26 GMT

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

Re: problem with dates

Amir — Thu, 06 Sep 2018 09:58:07 GMT

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.

Re: problem with dates

Dany1 — Thu, 06 Sep 2018 10:06:42 GMT

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!!

Re: problem with dates

Kurt_Bremser — Thu, 06 Sep 2018 10:07:43 GMT

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

Re: problem with dates

RW9 — Thu, 06 Sep 2018 10:09:53 GMT

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.

Re: problem with dates

Dany1 — Thu, 06 Sep 2018 10:15:43 GMT

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

Re: problem with dates

gamotte — Thu, 06 Sep 2018 10:46:50 GMT

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;

Re: problem with dates

Dany1 — Thu, 06 Sep 2018 10:53:45 GMT

Thank you!!!!!

Re: problem with dates

Amir — Thu, 06 Sep 2018 12:57:50 GMT

Hi,

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

Thanks,

Amir.

Re: problem with dates

Kurt_Bremser — Thu, 06 Sep 2018 13:11:41 GMT

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.

Re: problem with dates

RW9 — Thu, 06 Sep 2018 13:53:39 GMT

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