cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
V_Ra
‎05-20-2020
  • 3 Posts
  • 0 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: Summarizing data using Proc SQL and CASE expression

by Calcite | Level 5 in SAS Programming
‎05-20-2020 05:26 PM
‎05-20-2020 05:26 PM
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/624905#M184140) 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     ... View more

Re: Summarizing data using Proc SQL and CASE expression

by Calcite | Level 5 in SAS Programming
‎05-20-2020 02:27 PM
‎05-20-2020 02:27 PM
Thank you! Once again, thank you for contributing to this learning community. VR ... View more

Summarizing data using Proc SQL and CASE expression

by Calcite | Level 5 in SAS Programming
‎05-20-2020 02:20 PM
‎05-20-2020 02:20 PM
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 ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎05-20-2020 09:22 PM
Quick links