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

[HansSteenhuis]

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.

[Astounding]

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;

[HansSteenhuis]

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

[Reeza]

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

---

[Astounding · Accepted solution]

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.

[HansSteenhuis]

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;

[HansSteenhuis]

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;