topic Re: Need Grouping with all data fields in SAS Programming

Need Grouping with all data fields

Rahul_SAS — Tue, 31 Jul 2018 17:50:50 GMT

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;

Re: Need Grouping with all data fields

novinosrin — Tue, 31 Jul 2018 18:04:30 GMT

Can you plz include sample (preferably small samples of your input and your expected(desired) output

Re: Need Grouping with all data fields

ballardw — Tue, 31 Jul 2018 18:41:44 GMT

From the spreadsheet, which is not a SAS data set and may differ notably from your SAS data set, the "date" variable is a date time. If your data set has that variable continuing to be a datetime then perhaps you want to use

select
distinct pcode, empname, mname, team, caseid, type, ser, status, datepart(date) as date

Assign a preferred format to this date variable.

Since your spreadsheet had times down to fractions of a second that would be a very likely reason that the grouping wasn't as you desired as each second or fraction would be a different group

Re: Need Grouping with all data fields

PGStats — Tue, 31 Jul 2018 19:00:09 GMT

It seems like it should work if your subquery was:

select 
    pcode, 
    empname, 
    mname, 
    team, 
    date,
    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, date

Re: Need Grouping with all data fields

Rahul_SAS — Tue, 31 Jul 2018 19:38:29 GMT

please check the smaller HAVE and WANT data.

Re: Need Grouping with all data fields

ballardw — Tue, 31 Jul 2018 20:54:01 GMT

@Rahul_SAS wrote:

please check the smaller HAVE and WANT data.

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.