need distinct count for all group by Variable

[Rahul_SAS]

Hi Experts,

In the below code I am trying to get distinct Caseid where status are QCCOMP, QCCOMPERR and caseid is not missing but don't know how to get
"case when status In ('QCCOMP') AND caseid NOT IN ('') THEN distinct caseid else 0 end as QCCOMP"

For example pcode 1008336 has the caseid count should be 3 but i am getting 6 for QCCOMP status.
pcode 1009255 has the caseid count should be 2 but i am getting 4 for QCCOMP status etc.

Further I need to calculate (QCCOMP/QCCOMP+QCCOMPERR) with respect to all grouped variables(pcode, emp_name, supervisor, team, qcdate, hub).

proc sql ;
create table stag1 as
select distinct pcode, emp_name, supervisor,  team, qcdate, caseid, status, hub
from Sample_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 QCCOMP,
case when status In ('QCCOMPERR') AND caseid NOT IN ('') THEN 1 else 0 end as QCCOMPERR,
from stag1
group by pcode, emp_name, supervisor,  team, status, hub;
quit;

Please help.

Thanks
Rahul

[tsap]

If I am understanding your request correctly the code below should get you the results mentioned:

PROC SQL;
CREATE TABLE WORK.Stag1 (DROP=Employee_Name Supervisor_Name  CaseAccountableTeam) as
	SELECT DISTINCT ProjectCode, Employee_Name, Supervisor_Name,  CaseAccountableTeam, QCCompleteDate, SafetyCaseID, QCCompleteStatusName, ProcessingHub
	FROM WORK.Sample_Data;
QUIT;

PROC SORT DATA=WORK.Stag1 NODUPKEY; BY _ALL_; RUN;
 

PROC SQL;
CREATE TABLE WORK.Stag2 AS
	SELECT DISTINCT
	  	  ProjectCode
		, SUM(CASE WHEN QCCompleteStatusName IN('QCCOMP') 		AND SafetyCaseID NOT IN('') THEN 1 ELSE 0 END)		AS QCCOMP
		, SUM(CASE WHEN QCCompleteStatusName IN('QCCOMPERR') 	AND SafetyCaseID NOT IN('') THEN 1 ELSE 0 END)		AS QCCOMPERR

	FROM WORK.Stag1
	GROUP BY ProjectCode;
QUIT;

In order to get the counts that you were looking for, I needed to remove these variables (Employee_Name, Supervisor_Name, CaseAccountableTeam) from the SQL Step performing the grouping counts.

These had variations across the observations that if included in the grouping, would appear as a distinct observation and so would give you a count of 6 when you expected 3 and 4 when you expected 2. Without them, the counts align to you volume expectations. If those must be included in that step, then you will not get the counts you think you should, due to their variance.

I wasn't sure exactly what your second request was looking to accomplish specifically. I understand the calculation logic, just not how you wanted that value determined for all of the grouped variables.

if you add:

	
	, (CALCULATED QCCOMP/(CALCULATED QCCOMP + CALCULATED QCCOMPERR))	AS test

 to the select statement above, it will generate values for each of the rows generated there. Hopefully this is what you were looking to find.

[Rahul_SAS]

Hi tsap,

Thank you for you help.

Your solution solves my problem partially but not completely.

My actual requirement to get it for all variables what you did for project only. I need to upload this table on LASR for a dashboard where i have to give all grouped variable as filter and this is possible only when it could produce counts based on all grouped variables.

Thanks

Rahul

[ballardw]

---

@Rahul_SAS wrote:

Hi Experts,

In the below code I am trying to get distinct Caseid where status are QCCOMP, QCCOMPERR and caseid is not missing but don't know how to get
"case when status In ('QCCOMP') AND caseid NOT IN ('') THEN distinct caseid else 0 end as QCCOMP"

For example pcode 1008336 has the caseid count should be 3 but i am getting 6 for QCCOMP status.
pcode 1009255 has the caseid count should be 2 but i am getting 4 for QCCOMP status etc.

Further I need to calculate (QCCOMP/QCCOMP+QCCOMPERR) with respect to all grouped variables(pcode, emp_name, supervisor, team, qcdate, hub).

proc sql ;
create table stag1 as
select distinct pcode, emp_name, supervisor,  team, qcdate, caseid, status, hub
from Sample_Data;
quit;

If that spreadsheet is supposed to represent your starting data then you need to recreate your post to at least reference columns in the spreadsheet. Not a single variable you reference in your code above appears as a column heading in the spreadsheet. So we have to make a lot of guesses at to what role any of the columns  plays. Guesses are a very poor approach to any programming problem.

Provide example data in the form of a data step.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... 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.

That way we have a chance of matching variables to code posted.

[Rahul_SAS]

appologies.

dropped that old spreadsheet and uploaded it with correction.