Hi Experts,
I am trying to group the given data by pcode, empname, mname, team, date in the final data "STAG3 " but not getting the correct result. while, if i don't include date, it gives correct value.
Please look into the below code and correct me if i am making any mistake.
Sample data is attached.
Need Grouping by pcode, empname, mname, team, date data fields for CasePercentage .
Is there any efficient way to do this?
proc sql ; create table stag1 as select distinct pcode, empname, mname, team, caseid, type, ser, status, date from report_data; quit;
proc sql; create table stag2 as select stag1.*, case when status In ('QCCOMP') AND caseid NOT IN ('') THEN '1' else '0' end as Case_ID_for_QCCOMP, case when status In ('QCCOMPERR') AND caseid NOT IN ('') THEN '1' else '0' end as Case_ID_for_QCCOMPERR, case when status In ('QCCOMP','QCCOMPERR') AND caseid NOT IN ('') THEN '1' else '0' end as Total_Case_IDs from stag1; quit;
proc sql; create table STAG3 as select distinct pcode, empname, mname, team, (COMP/(COMP+COMPERR))*100 AS CasePercentage from (select distinct pcode, empname, mname, team, sum(input(Case_ID_for_QCCOMP,BEST12.)) as COMP, sum(input(Case_ID_for_QCCOMPERR,BEST12.)) as COMPERR FROM stag2 group by pcode, empname, mname, team
) as test; quit;
... View more