Sql with multiple where clause

[HDSimo]

Hi,

this is an ipotetic DS

data x;

input a b;

cards;

5 1

6 2

2 4

5 3

6 1

4 2

8 3

9 2

;run;

Is it possible with only one proc SQL calculate count of a and count of a where b=1?

Thank you

[Tom]

Not sure what you want exactly, but perhaps using SUM will work.

proc sql noprint ;

   create table counts as

    select distinct A,count(*) as count,sum(b=1) as countb1

     from x

     group by a

   ;

quit;

Obs    a    count    countb1

1     2      1         0

2     4      1         0

3     5      2         1

4     6      2         1

5     8      1         0

6     9      1         0

[art297 · Accepted solution]

I, too, am not sure what you want.  The way I interpreted your question I think you are looking for something like:

proc sql;

  select (select count(a)

    from x

      where b eq 1 ) as part_a,

        count(a) as all_a

         from x

  ;

quit;

[HDSimo]

Thank you Art297. It works!

[Ksharp]

data x;
input a b;
cards;
5 1
6 2
2 4
5 3
6 1
4 2
8 3
9 2
;run;
proc sql;
 select count(*) as count_a,
  sum(case when(b=1) then 1 else 0 end) as part_a
  from x;
quit;

Ksharp