08-29-2016 06:23 AM
I would like to ask a question to you. It is related to Proc Sql statement and Case When. I want to count the number "1" values in the"Numeric" variable also by grouping "Numeric". However, in the following example, the query brings two rows but actually, I expect just one row. Can somebody help me by checking the following sample, please?
Data Have; Length Character $ 8 Numeric 8; Infile Datalines Missover; Input Character Numeric; Datalines; 33 1 33 2 33 3 33 3 33 1 33 2 33 2 33 3 33 3 ; Run; PROC SQL; Create Table Want As Select Distinct Character ,Case When Numeric=1 Then Count(Numeric) End As CountNumeric From Have Where Character='33' Group By Numeric; QUIT;
My desired output;
08-29-2016 06:33 AM
--One way PROC SQL; Create Table Want As Select Character ,count( numeric) as count_numeric From Have Where Character='33' and numeric =1 Group By character; QUIT; --Alternate way PROC SQL; Create Table Want2 As Select Character ,sum( case when numeric=1 then 1 end ) as count_numeric From Have Where Character='33' Group By character; QUIT;
08-29-2016 06:42 AM
There are couple of problem in the query
1. Use of aggregate function and use of distinct at the same time do not make any sense. Because aggregate function itself result one row per group.
2. The query need to group by using Character column but it has used group by Numeric. The Group by should used on grouping variable which is Character in this case.
3. Count function used in case when then statement do not make any sense.
08-29-2016 06:57 AM
use HAVING clause: Data Have; Length Character $ 8 Numeric 8; Infile Datalines Missover; Input Character Numeric; Datalines; 33 1 33 2 33 3 33 3 33 1 33 2 33 2 33 3 33 3 ; Run; PROC SQL; Select Character,Numeric,count(*) As CountNumeric From Have Group By Character,Numeric having Character='33' and Numeric=1; QUIT;