I have a dataset which is as follows:
DATE(IN DD:MMM) | SERVICE REQUEST | PROCESSING TIME(HH:MM) |
24-Nov | SRQST1234 | 01:15 |
25-Nov | SRQST1235 | 02:15 |
26-Nov | SRQST1236 | 03:15 |
27-Nov | SRQST1237 | 04:15 |
28-Nov | SRQST1238 | 05:15 |
29-Nov | SRQST1239 | 06:15 |
30-Nov | SRQST1240 | 07:15 |
01-Dec | SRQST1241 | 08:15 |
02-Dec | SRQST1242 | 09:15 |
03-Dec | SRQST1243 | 10:15 |
Need a report on this which will be like :
TIME INTERVAL - 30 MINS | TOTAL | ||||||
Date | 0-30 | 30-60 | 60-90 | 90-120 | <2HRS | COUNT | AVG PROCESSIN TIME |
A | Count and % | Count and % | Count and % | Count and % | Count and % | Total Count | Average Processing Time of all Servoce Request on that particulat date A. |
Hi,
below my attempt at creating your desired result. I had to use Proc Tabulate instead of Proc Report though.
proc format ;
value minintv 0-30 = '0-30'
30-60 = '30-60'
60-90 = '60-90'
90-120 = '90-120'
120-high = '120-'
;
value hourintv 0-120 = '<2HRS'
120-high = '>2HRS'
;
run;
data requests;
attrib date informat=date7. format=date9. ;
attrib cdate informat=$6. ;
attrib service_request informat=$20. ;
attrib processing_time informat=time5. format=time5. ;
input CDATE SERVICE_REQUEST PROCESSING_TIME;
date = input(catx('-',cdate,'2016'),date11.);
drop cdate;
minutes = minute(processing_time)+(hour(processing_time)*60);
mingrp = put(minutes,minintv.);
hours2 = put(minutes,hourintv.);
cards;
24-Nov SRQST1234 00:15
24-Nov SRQST1234 01:15
25-Nov SRQST1235 01:15
25-Nov SRQST1235 02:15
26-Nov SRQST1236 00:25
26-Nov SRQST1236 03:15
27-Nov SRQST1237 04:15
28-Nov SRQST1238 05:15
29-Nov SRQST1239 06:15
30-Nov SRQST1240 07:15
01-Dec SRQST1241 08:15
02-Dec SRQST1242 09:15
03-Dec SRQST1243 10:15
;
run;
proc sort data=work.requests;
by date minutes;
options missing='0';
proc tabulate data=work.requests order=data ;
class date mingrp hours2;
var minutes processing_time;
table date='Date'*f=ddmmyy10.,
mingrp='Time interval 30 minutes'*
(N='Count'*f=8. PCTN<mingrp>='Percent'*f=5.1)
hours2='Less than or greater than 2 hours'*f=hourintv.*
(N='Count'*f=8. PCTN<hours2>='Percent'*f=5.1)
N='Count'*f=8. processing_time='Avg Processing time'*MEAN=''*f=time5.
;
run;
The result will look like this:
Time interval 30 minutes | Less than or greater than 2 hours | Count | Avg Processing time | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
0-30 | 60-90 | 120- | <2HRS | >2HRS | ||||||||
Count | Percent | Count | Percent | Count | Percent | Count | Percent | Count | Percent | |||
Date | 1 | 50.0 | 1 | 50.0 | 0 | 0 | 2 | 100.0 | 0 | 0 | 2 | 0:45 |
24NOV2016 | ||||||||||||
25NOV2016 | 0 | 0 | 1 | 50.0 | 1 | 50.0 | 1 | 50.0 | 1 | 50.0 | 2 | 1:45 |
26NOV2016 | 1 | 50.0 | 0 | 0 | 1 | 50.0 | 1 | 50.0 | 1 | 50.0 | 2 | 1:50 |
27NOV2016 | 0 | 0 | 0 | 0 | 1 | 100.0 | 0 | 0 | 1 | 100.0 | 1 | 4:15 |
28NOV2016 | 0 | 0 | 0 | 0 | 1 | 100.0 | 0 | 0 | 1 | 100.0 | 1 | 5:15 |
29NOV2016 | 0 | 0 | 0 | 0 | 1 | 100.0 | 0 | 0 | 1 | 100.0 | 1 | 6:15 |
30NOV2016 | 0 | 0 | 0 | 0 | 1 | 100.0 | 0 | 0 | 1 | 100.0 | 1 | 7:15 |
01DEC2016 | 0 | 0 | 0 | 0 | 1 | 100.0 | 0 | 0 | 1 | 100.0 | 1 | 8:15 |
02DEC2016 | 0 | 0 | 0 | 0 | 1 | 100.0 | 0 | 0 | 1 | 100.0 | 1 | 9:15 |
03DEC2016 | 0 | 0 | 0 | 0 | 1 | 100.0 | 0 | 0 | 1 | 100.0 | 1 | 10:15 |
Best regards,
Michael
Thanks Michael,
I need to built a graph on this ..
Date-WIse Graph..
Type of Graph - 100% Stacked Overflow(Stacked Bars containg % of request processed in evry 30 mins).
And two linear graphs showing the trend of total count and Avg Volume..?
Hope U got my requirement..
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.