BookmarkSubscribeRSS Feed
rokuface
Calcite | Level 5

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. 

4 REPLIES 4
Reeza
Super User

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
Calcite | Level 5

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
PROC Star

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
Super User

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 






sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1195 views
  • 0 likes
  • 3 in conversation