Hi All,
I need some help formatting dates.
data date_duration13;
set c6;
by id;
retain first_date;
if first.id then first_date = date ;
informat date ddmmyy10.;
format date ddmmyy10.;
if last.id then do;
last_date = date ;
informat date ddmmyy10.;
format date ddmmyy10.;
duration =last_date-first_date ;
output;
end;
run;
This is the code i used between first date date last date.
Obs id date price category first_date last_date duration
1 9801134 12/11/1999 14.11 books 14559 14560 1
2 9801234 12/01/1998 130.98 books 13889 13891 2
3 9802234 11/10/1997 100 apparel 13799 13798 -1
4 9805556 10/11/1999 18.9 books 13187 14558 1371
I would like to see first_date,lase_date in dateformat like ddmmyy and also like to calculate average no of days between purchases.
something like this?
data date_duration13;
set c6;
by id;
format
first_date ddmmyy10.
last_date ddmmyy10.
orders 10.
Tot_Duration 10.
;
retain
first_date
orders
Tot_Duration
tmpOrderDate
;
if first.id then do;
first_date = date;
orders=1;
Tot_Duration=0;
tmpOrderDate = date;
end;
else do;
orders+1;
Tot_Duration = Tot_Duration + date - tmpOrderDate;
tmpOrderDate=date;
end;
if last.id then do;
last_date = date ;
duration =last_date-first_date ;
AvgDuration = Tot_Duration / orders;
output;
end;
run;
Add format statements for your first_date and last_date variables.
e.g.
format first_date ddmmyy10.;
something like this?
data date_duration13;
set c6;
by id;
format
first_date ddmmyy10.
last_date ddmmyy10.
orders 10.
Tot_Duration 10.
;
retain
first_date
orders
Tot_Duration
tmpOrderDate
;
if first.id then do;
first_date = date;
orders=1;
Tot_Duration=0;
tmpOrderDate = date;
end;
else do;
orders+1;
Tot_Duration = Tot_Duration + date - tmpOrderDate;
tmpOrderDate=date;
end;
if last.id then do;
last_date = date ;
duration =last_date-first_date ;
AvgDuration = Tot_Duration / orders;
output;
end;
run;
Good thinking, and very close. Remember, the number of durations is ORDERS-1, not ORDERS. In that case, you might have to check for there being only one order for an ID as well.
Thank you all for your input
I got right output.
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.