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