this might be tricky in sas,how to count times for each average time interval
below is the case for mssql,and could create a colum interval_time for each customer then sum up.how to achive it in sas?data step or proc sql
CREATE TABLE customer_data (customer_id BIGINT, date DATE, time time, answer VARCHAR(100), missed_call_type VARCHAR(100)); INSERT INTO customer_data VALUES (101, '2018/8/3', '12:13:00', 'no', 'employee'), (102, '2018/8/3', '12:15:00', 'no', 'customer'), (103, '2018/8/3', '12:20:00', 'no', 'employee'), (102, '2018/8/3', '15:15:00', 'no', 'customer'), (101, '2018/8/3', '18:15:00', 'no', 'employee'), (105, '2018/8/3', '18:18:00', 'no', 'customer'), (102, '2018/8/3', '19:18:00', 'no', 'employee') select cd.customer_id, answer, missed_call_type, CAST(CAST(cd.date as VARCHAR(10))+' ' +CAST(cd.time as VARCHAR(10)) as datetime) as date, ROW_NUMBER() OVER(PARTITION BY cd.customer_id ORDER BY date desc, time desc) as ranks INTO #temP from customer_data cd order by cd.customer_Id, ranks; select AVG(DATEDIFF(MINUTE, x1.date, x2.date)) as avg_mins from #temP x1 INNER JOIN #temP x2 ON x1.customer_id = x2.customer_id WHERE x2.ranks = (x1.ranks-1)
-------------------------------------------------------------------------update----------------------------------------------------------------------------------------
cst_id | minutes | time_interval(logic for calculate it) |
101 | 362 | 18:15:00-12:13:00=6hours and 2mintes |
102 | 423 | [(15:15:00-12:15:00)+(19:18:00-15:15:00)]/2=7hours 3minutes |
this is the procss,if there is anything unclear please tell.thanks!
Any calculation that relies on data ordering is simpler with a datastep in SAS:
data cd;
set customer_data;
date = dhms(date, 0, 0, time);
format date datetime17.;
drop time;
run;
proc sort data=cd; by customer_id date; run;
data avgTimes;
do nbInt = 1 by 1 until (last.customer_id);
set cd; by customer_id;
if first.customer_id then firstTime = date;
if last.customer_id then lastTime = date;
end;
if nbInt > 1 then do;
totTime = lastTime - firstTime;
avgTime = totTime / (nbInt - 1);
output;
end;
format totTime mmss8. avgTime time9.;
drop nbInt lastTime firstTime date;
run;
Hello @Geo-,
Your question requires more details before experts can help. Can you revise your question to include more information?
Review this checklist:
To edit your original message, select the "blue gear" icon at the top of the message and select Edit Message. From there you can adjust the title and add more details to the body of the message. Or, simply reply to this message with any additional information you can supply.
SAS experts are eager to help -- help them by providing as much detail as you can.
This prewritten response was triggered for you by fellow SAS Support Communities member @PGStats
.Any calculation that relies on data ordering is simpler with a datastep in SAS:
data cd;
set customer_data;
date = dhms(date, 0, 0, time);
format date datetime17.;
drop time;
run;
proc sort data=cd; by customer_id date; run;
data avgTimes;
do nbInt = 1 by 1 until (last.customer_id);
set cd; by customer_id;
if first.customer_id then firstTime = date;
if last.customer_id then lastTime = date;
end;
if nbInt > 1 then do;
totTime = lastTime - firstTime;
avgTime = totTime / (nbInt - 1);
output;
end;
format totTime mmss8. avgTime time9.;
drop nbInt lastTime firstTime date;
run;
Come to think of it, this problem doesn't rely on ordering. It can also be solved simply with SQL:
data cd;
set customer_data;
date = dhms(date, 0, 0, time);
format date datetime17.;
drop time;
run;
proc sql;
create table avgTimes as
select
customer_id,
range(date) as totTime format=mmss8.,
range(date) / (count(date)-1) as avgTime format=time9.
from cd
group by customer_id
having count(date) > 1;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.