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

[rokuface]

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. 

[Reeza]

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. 

---

[rokuface]

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 

[Astounding]

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;

[Reeza]

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 

---