Summarizing data using Proc SQL and CASE expression

[V_Ra]

Long-time user of the forum. Thank you for your solutions. VR

Problem -

I am not getting the results I want from the following code.

Objective - Count the number of reviews/Complaints/Review/complaints (var-Source) for each Facility (two facilities listed below- 11,12), for each year (var-Def_Year).

Here is the data and code.

data deficiencies;
length Source $32;
input Deficiency_Id Source$ Def_Year FacilityID ;
datalines;
302311443 Review 2017 11
302311444 Review 2017 11
302311442 Review 2018 11
302311440 Review 2018 11
302311441 Review 2018 11
302326589 Review 2018 12
302326588 Review 2018 12
302326592 Review 2018 12
302326593 Review 2018 12
302326590 Review 2018 12
302326591 Review 2018 12
302326594 Review 2018 12
302326595 Review 2018 12
302326587 Complaint 2018 12
302326586 Review 2019 12
302326582 Review/Complaint 2019 12
302326583 Review/Complaint 2019 12
302326585 Review/Complaint 2019 12
302326584 Review/Complaint 2019 12
;
run;

*Calculate number of deficiencies by year by facility by Source of deficiency;
proc sql;
CREATE TABLE Qtrly_defsA2 as
SELECT
Source

,Def_Year

,FacilityID

,count(distinct Deficiency_ID) as Total_Deficiencies
,count( unique case when Source = "Review" then 1 else 0 end) as Review_Defs
,count( distinct case when Source = "Complaint" then 1 else 0 end) as Complaint_Defs
,count( distinct case when Source = "Review/Complaint" then 1 else 0 end) as RevComp_Defs
FROM deficiencies
Group by FacilityID, Def_Year
order by FacilityID;
QUIT;

Desired results -

Source	Def_Year	FacilityID	Total_Deficiencies	Review_Defs	Complaint_Defs	RevComp_Defs
Review	2017	11	2	2	0	0
Review	2017	11	2	2	0	0
Review	2018	11	3	3	0	0
Review	2018	11	3	3	0	0
Review	2018	11	3	3	0	0
Complaint	2018	12	9	8	1	0
Review	2018	12	9	8	1	0
Review	2018	12	9	8	1	0
Review	2018	12	9	8	1	0
Review	2018	12	9	8	1	0
Review	2018	12	9	8	1	0
Review	2018	12	9	8	1	0
Review	2018	12	9	8	1	0
Review	2018	12	9	8	1	0
Review	2019	12	5	1	0	4
Review/Complaint	2019	12	5	1	0	4
Review/Complaint	2019	12	5	1	0	4
Review/Complaint	2019	12	5	1	0	4
Review/Complaint	2019	12	5	1	0	4

[RyanK · Accepted solution]

Change your count to sum, remove the distinct

[V_Ra]

Thank you! Once again, thank you for contributing to this learning community.

VR

[novinosrin]

Hi @V_Ra  Sorry that I am late to the party and your question has been solved. I am sincerely glad. Just an addition that it's worth some attention. Since you are conditionally assigning 1s and 0s , you could consider a simple boolean expression and avoid a full extended CASE WHEN.

For example,

,sum( Source = "Review" ) as Review_Defs
,sum(Source = "Complaint" ) as Complaint_Defs
,sum(Source = "Review/Complaint") as RevComp_Defs

Lazy folks like me always look for convenience 🙂

[V_Ra]

Thanks @novinosrin . We learn something new every day!

I checked out a previous post (https://communities.sas.com/t5/SAS-Programming/sql-count-using-case-when-group-by-inside-count/m-p/6...) using "id" but got an error.

ERROR: The following columns were not found in the contributing tables: id.

Could I get a reference on how to use "id" in PROC SQL? Thanks in advance.

VR