topic How to loop through ID numbers and calculate number of days between two dates? in SAS Programming

How to loop through ID numbers and calculate number of days between two dates?

rokuface — Thu, 15 Mar 2018 15:47:48 GMT

I have ID numbers with the date of purchase:

ID Number Purchase Date and Time

1234 01OCT2008:10:00:13

1234 09SEP2009:16:00:23

1234 14MAY2012:10:00:27

567 07JAN2009:16:00:20

789 ....

789

234

And so on and so forth. I want to loop through the purchase date and time by ID numbers and count the number of days between the purchase date. So basically I want to ask " How many days ago did the same person make that purchase. So I want a count of days between first record and second, then the second and third for each ID number.

My code isn't that great now,

DATA COUNTS1;
SET SUMMARY; BY ID_NUMBER;

FIRST_DATE= DATEPART(FIRST.PURCHASE_DATE);
END_DATE = DATEPART(LAST.PURCHASE_DATE);
DO I= FIRST_DATE TO END_DATE;
DAYS= INTCK('DAYS',FIRST_DATE,END_DATE);
OUTPUT;
END;
PUT DAYS;
RUN;

New to SAS, so please help me out.

Re: How to loop through ID numbers and calculate number of days between two dates?

Reeza — Thu, 15 Mar 2018 15:53:31 GMT

No loops per se.

SAS loops through data line by line so you don't need any loops. It also allows you to identify the first record of a group using BY and FIRST.

DATA COUNTS1;
SET SUMMARY; 

BY ID_NUMBER;

*Method 1 - use DIF() function + math;

Duration1 = DIF(purchase_date) / (60*60*24); *this will produce the difference in seconds + do some math to get days;

*Method 2 - use LAG() and INTNX approach;
Prev_Date = lag(purchase_date);
Duration2 = intnx('dtday', purchase_date, prev_date);

if first.ID then call missing(duration1, duration2); 

RUN;

There are many other ways to do this as well.

@rokuface wrote:

I have ID numbers with the date of purchase:

ID Number Purchase Date and Time

1234 01OCT2008:10:00:13

1234 09SEP2009:16:00:23

1234 14MAY2012:10:00:27

567 07JAN2009:16:00:20

789 ....

789

234

234

234

And so on and so forth. I want to loop through the purchase date and time by ID numbers and count the number of days between the purchase date. So basically I want to ask " How many days ago did the same person make that purchase. So I want a count of days between first record and second, then the second and third for each ID number.

My code isn't that great now,

DATA COUNTS1;
SET SUMMARY; BY ID_NUMBER;

FIRST_DATE= DATEPART(FIRST.PURCHASE_DATE);
END_DATE = DATEPART(LAST.PURCHASE_DATE);
DO I= FIRST_DATE TO END_DATE;
DAYS= INTCK('DAYS',FIRST_DATE,END_DATE);
OUTPUT;
END;
PUT DAYS;
RUN;

New to SAS, so please help me out.

Re: How to loop through ID numbers and calculate number of days between two dates?

rokuface — Thu, 15 Mar 2018 16:04:09 GMT

Yeah however, it looks like it's not giving me the right solution, days don't look right.

I got:

data counts1;

set summary;

by Id_number;

keep days;

purchase= datepart(purchase_date);

prev_date= lag(purchase);

days= intnx=('dtday', purchase, prev_date);

put days;

run;

Giving me weird numbers though

Re: How to loop through ID numbers and calculate number of days between two dates?

Astounding — Thu, 15 Mar 2018 16:32:13 GMT

I don't think @Reeza's solutions intended to use DATEPART. It still can be solved in that way, however. Making small adjustments to your latest program:

data counts1;

set summary;

by Id_number;

purchase= datepart(purchase_date);

days = dif(purchase);

if first.id_number then days=.;

put days;

run;

Re: How to loop through ID numbers and calculate number of days between two dates?

Reeza — Thu, 15 Mar 2018 17:06:29 GMT

Please look up the INTNX function. The first parameter is the interval you're looking for, ie day, months, weeks or some variation.

Since you had datetimes I specified DTDAY which is date time days.

If you have dates you would instead specify DAY.

@rokuface wrote:

Yeah however, it looks like it's not giving me the right solution, days don't look right.

I got:

data counts1;

set summary;

by Id_number;

keep days;

purchase= datepart(purchase_date);

prev_date= lag(purchase);

days= intnx=('dtday', purchase, prev_date);

put days;

run;

Giving me weird numbers though