DATA Step, Macro, Functions and more

SAS Code to calculate difference (in days) between last and second-last date

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

SAS Code to calculate difference (in days) between last and second-last date

Hello,

Example of data:

 

ID        Date                            amount_of_days

1          01-01-2017                

1          04-01-2017                

1          12-01-2017                 12

2          01-04-2017

2          01-05-2017                 31

3          01-02-2018

3          05-02-2018

3          11-02-2018                            

3          21-02-2018                 10

 

is it  possible to calculate the amount of days between the last and second-last date within the ID?

 

Thank you very much for any comments.

 

 

 

 


Accepted Solutions
Solution
‎02-21-2018 02:15 PM
Super User
Posts: 6,644

Re: SAS Code to calculate difference (in days) between last and second-last date

[ Edited ]
Posted in reply to HansSteenhuis

Well, your formula is not consistent.  Do you want to count both the first and last day in the total number of days?  At any rate, you can certainly do this:

 

data want;

set have;

by id;

amount_of_days = dif(date);

if first.id=1 then amount_of_days = 0;

run;

 

But it's not clear from your example if  you want to use:

 

amount_of_days = dif(date) + 1;

 

Test the formulas, to see which one is right for you.

View solution in original post


All Replies
Super User
Posts: 6,644

Re: SAS Code to calculate difference (in days) between last and second-last date

Posted in reply to HansSteenhuis

Your dates have to be actual SAS dates, not character strings.  As long as your data is in order by ID:

 

data want;

set have;

by id;

amount_of_days = dif(date);

if last.id=0 or first.id=1 then amount_of_days = .;

run;

New Contributor
Posts: 4

Re: SAS Code to calculate difference (in days) between last and second-last date

Posted in reply to Astounding

thank you very much, it works :-)

 

Is is also possible to calculate the amount of days like this Example:

 

Example of data:

 

ID        Date                            number_of_days

1          01-01-2017                 0

1          04-01-2017                 4

1          12-01-2017                 9

2          01-04-2017                 0

2          01-05-2017                 2

3          01-02-2018                 276

3          05-02-2018                 5

3          11-02-2018                 7         

3          21-02-2018                 11

Super User
Posts: 23,357

Re: SAS Code to calculate difference (in days) between last and second-last date

Posted in reply to HansSteenhuis

Use the DIF() function and then set it to 0 at the first of each group using FIRST.

 


HansSteenhuis wrote:

thank you very much, it works :-)

 

Is is also possible to calculate the amount of days like this Example:

 

Example of data:

 

ID        Date                            number_of_days

1          01-01-2017                 0

1          04-01-2017                 4

1          12-01-2017                 9

2          01-04-2017                 0

2          01-05-2017                 2

3          01-02-2018                 276

3          05-02-2018                 5

3          11-02-2018                 7         

3          21-02-2018                 11


 

Solution
‎02-21-2018 02:15 PM
Super User
Posts: 6,644

Re: SAS Code to calculate difference (in days) between last and second-last date

[ Edited ]
Posted in reply to HansSteenhuis

Well, your formula is not consistent.  Do you want to count both the first and last day in the total number of days?  At any rate, you can certainly do this:

 

data want;

set have;

by id;

amount_of_days = dif(date);

if first.id=1 then amount_of_days = 0;

run;

 

But it's not clear from your example if  you want to use:

 

amount_of_days = dif(date) + 1;

 

Test the formulas, to see which one is right for you.

New Contributor
Posts: 4

Re: SAS Code to calculate difference (in days) between last and second-last date

Posted in reply to Astounding
Thanks you. I now also use this code for the calculation

data want;
set have;
by id;
amount_of_days = dif(date);
if first.id=1 then amount_of_days = 0;
run;
New Contributor
Posts: 4

Re: SAS Code to calculate difference (in days) between last and second-last date

Posted in reply to Astounding
Thank you. I also use this code for the calculation

data want;
set have;
by id;
amount_of_days = dif(date);
if last.id=0 or first.id=1 then amount_of_days = .;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 95 views
  • 0 likes
  • 3 in conversation