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.
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.
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
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;
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
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.
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.