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

Reply
New Contributor
Posts: 2

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

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. 

Super User
Posts: 23,663

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

[ Edited ]

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. 


 

New Contributor
Posts: 2

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

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 

Super User
Posts: 6,751

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

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;

Super User
Posts: 23,663

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

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 






Ask a Question
Discussion stats
  • 4 replies
  • 90 views
  • 0 likes
  • 3 in conversation