Proc Sql WHERE clause for only one variable while calculating other variables without condition

[Lena_PA]

I'm trying to create a 

Proc Sql ;
create table NEW as select *, sum (VARIABLE_1) as VAR1_SUM,  count (distinct VARIABLE_2 where VARIABLE3=1) as VAR2_CNT
from HAVE group by week, store;
quit;

This is my current code and would not run.. Any suggestion? 

[Tom]

You probably want to use a CASE clause.

count(case when (VARIABLE3=1) then VARIABLE_2 end ) as VAR2_CNT

Which will count how many observations have VARIABLE3=1 and VARIABLE_2 not missing. 

[Lena_PA]

Thank you so much! I would like to count the distinct number of VARIABLE_2 (not the total observation) where VARIABLE_3=1. 

So the Proc Sql will give me the value 3 for VAR2_CNT (the VARIABLE_2 where variable3=1, 100,200,300) not 5 (total observation where variable3=1)

VARIABLE1	VARIABLE2	VARIABLE3
15	100	1
19	200	1
20	300	1
40	400	0
15	500	0
16	100	1
0	200	1
0	300	0
19	400	0

[Tom]

If you want the count of the distinct values then just tell SQL to do that instead.

count(distinct case when (VARIABLE3=1) then VARIABLE_2 end ) as VAR2_CNT

[Tom · Accepted solution]

It really help to start with posting your input data.

data have;
  input var1-var3;
cards;
15 100 1
19 200 1
20 300 1
40 400 0
15 500 0
16 100 1
0 200 1
0 300 0
19 400 0
;

And your expected output data.

data want;
  input sum_var1 cnt_var2;
cards;
144 3
;

That way others can provide tested code:

proc sql;
create table want as
  select 
    sum(var1) as sum_var1
  , count(distinct case when(var3=1) then var2 end) as cnt_var2
  from have
;
quit;

[Lena_PA]

Thank you so much! I will make sure to include the input data to begin with next time! 

[ballardw]

Show the LOG with the code and all messages, notes, warning or errors. Copy the text from the log, on  the forum open a text box using the </> icon that appears above the message window and paste the text.

The text box is important because it will preserve the formatting of the text and diagnostic characters. The main message windows will reformat text and appearance in the message window is hard to tell where any of the diagnostics actually appeared.