topic Calculate Date Difference between Activity Rows in SAS Programming

Calculate Date Difference between Activity Rows

a_zacMD — Tue, 01 Dec 2020 18:44:26 GMT

I would like to calculate the date differences between activities for each ID, then tag those dates that happened within 30 days or less. All advice is greatly appreciated. My first step would be to sort based on id and activity date.

Here is an example of my data:

ID	Activity Date
1234	20200110
1234	20200120
1234	20200601
5678	20200110
5678	20200129
5678	20200220

Re: Calculate Date Difference between Activity Rows

PaigeMiller — Tue, 01 Dec 2020 18:49:37 GMT

First, you need to treat Activity_Date as a numeric variable in SAS, and convert it to a true SAS date value (which is the number of days since 01JAN1960). This done in my INPUT statement using the informat YYMMDD8. Then the difference between dates is just a subtraction.

data have;
input ID Activity_Date yymmdd8.;
cards;
1234 20200110
1234 20200120
1234 20200601
5678 20200110
5678 20200129
5678 20200220
;
data want;
    set have;
    by id;
    delta=activity_date-lag(activity_date);
    if first.id then delta=.;
run;

Re: Calculate Date Difference between Activity Rows

ballardw — Tue, 01 Dec 2020 18:49:59 GMT

Is that value an actual SAS date? Meaning a numeric value with a SAS date format applied such as YYMMDDN8. ?

If not the first thing will be to create an actual date value so that the comparisons are easy.

Re: Calculate Date Difference between Activity Rows

a_zacMD — Tue, 01 Dec 2020 19:22:09 GMT

No, the date value is 20200801 = YYYYMMDD.

Re: Calculate Date Difference between Activity Rows

PaigeMiller — Tue, 01 Dec 2020 19:28:25 GMT

@a_zacMD wrote:
No, the date value is 20200801 = YYYYMMDD.

So it has to be converted to a valid SAS date, which I showed how to do, and then the calculations are trivial.

Re: Calculate Date Difference between Activity Rows

ballardw — Tue, 01 Dec 2020 20:06:44 GMT

@a_zacMD wrote:
No, the date value is 20200801 = YYYYMMDD.

Actually no it isn't. If it were a real date then adding 1 to the data would make 20201231 20200101. If you get 20201232 that is why it is not a date. SAS dates are numeric but use Formats to display the number as the desired date. That also allows all the SAS date related functions to work.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354 has a PDF with much information about dates.

Re: Calculate Date Difference between Activity Rows

a_zacMD — Tue, 01 Dec 2020 20:27:37 GMT

thank you, this works great!!!

Re: Calculate Date Difference between Activity Rows

a_zacMD — Tue, 01 Dec 2020 20:28:46 GMT

Thank you for posting this!!