As I said, I would have to come back to this thread, regarding the fixed date comparison.
After the third comment in the code below, you will find a big data step. In the middle of the datastep, I will create a variable:
"%LET date=01APR2016 00:00:00;"
Next up in the same datastep I will calculate if the ACTIVE task ihas crossed his deadline or not. Since an ACTIVE task does not have a close_date, I will need to use a fixed date. The fixed date has to be the date of the day the unload was created and that's why it has to be the 1st of April 2016. Since the above quest to importing the dates resulted in a format of "ddMMMyyyy hh:mm:ss", I thought I could just write t he date variable like I did above. The result is not what I expected. When I run the entire query below, everything works as intended, but I have 0 active_overtime tasks for all users in the table. There are no errors, but I do think SAS does not recognize the format of the variable date. Could I just use the same strategy as the solution above? Simply add a format or informat in the %LET command?
/* Sorteer op intake gesprek. */
proc sql;
create table task_1 as
select task_handler, object_name, task_state, deadline, start_date, close_date, r_creation_date
from unload.task
where object_name = 'Intake gesprek'
order by task_handler;
quit;
/* Sorteer op status ACTIVE en DONE. */
proc sql;
create table task_2 as
select task_handler, object_name, task_state, deadline, start_date, close_date, r_creation_date
from task_1
where task_state in ('ACTIVE','DONE')
order by task_handler, task_state;
quit;
/* Alle ACTIVE en DONE taken optellen en overtime datum berekenen (ACTIVE_OVERTIME en DONE_OVERTIME). */
data task_3;
set task_2;
by task_handler;
retain
active
active_overtime
done
done_overtime
;
if first.task_handler
then do;
active=0;
active_overtime=0;
done=0;
done_overtime=0;
end;
/* Sinds er unloads worden gebruikt, wordt er geen live data gebruikt. Variabele "date" is een referentie datum, die overeen komt met de datum van de unloads. */
%LET date=01APR2016 00:00:00;
select (task_state);
when ('DONE') do;
done+1;
if close_date > deadline then done_overtime+1;
end;
when ('ACTIVE') do;
active+1;
if date() > deadline then active_overtime+1;
end;
otherwise ignore+1;
end;
if last.task_handler then output;
keep task_handler active active_overtime done done_overtime;
run;
/* Sorteer op status CANCEL en CANCELLED. */
proc sql;
create table cancel_1 as
select task_handler, object_name, task_state, deadline, start_date, close_date, r_creation_date
from unload.task
where task_state in ('CANCEL','CANCELLED')
order by task_handler, task_state;
quit;
/* Alle CANCEL en CANCELLED taken optellen. */
proc sql;
create table cancel_2 as
select task_handler, count(*) as total_cancel
from cancel_1
group by task_handler;
quit;
/* Sorteer op status WAITING. */
proc sql;
create table waiting_1 as
select task_handler, object_name, task_state, deadline, start_date, close_date, r_creation_date
from unload.task
where task_state in ('WAITING')
order by task_handler, task_state;
quit;
/* Alle WAITING taken optellen. */
proc sql;
create table waiting_2 as
select task_handler, count(*) as total_waiting
from waiting_1
group by task_handler;
quit;
/* Alle bovenstaande tabellen samenvoegen tot het eindresultaat. (ACTIVE, DONE, CANCEL en WAITING.) */
proc sql;
create table report_1 as
select A.object_name, A.group_members, B.active, B.active_overtime, B.done, B.done_overtime
from unload.employee as A, task_3 as B
where A.group_members=B.task_handler
;
quit;
proc sql;
create table report_2 as
select A.object_name, A.group_members, A.active, A.active_overtime, A.done, A.done_overtime, B.total_cancel as cancelled
from report_1 as A, cancel_2 as B
where A.group_members=B.task_handler
;
quit;
proc sql;
create table report.task as
select A.object_name, A.group_members, A.active, A.active_overtime, A.done, A.done_overtime, A.cancelled, B.total_waiting as waiting
from report_2 as A, waiting_2 as B
where A.group_members=B.task_handler
;
quit;
