BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dany1
Calcite | Level 5

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Amir
PROC Star

Hi,

 

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

 

Thanks,

Amir.

 

View solution in original post

10 REPLIES 10
Amir
PROC Star

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
Calcite | Level 5

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
Super User

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Rhodochrosite | Level 12

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
Calcite | Level 5

Thank you!!!!!

Amir
PROC Star

Hi,

 

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

 

Thanks,

Amir.

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1407 views
  • 0 likes
  • 5 in conversation