## Sql with multiple where clause

Solved
Occasional Contributor
Posts: 19

# Sql with multiple where clause

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

Accepted Solutions
Solution
‎10-13-2011 09:59 AM
PROC Star
Posts: 8,169

## Re: Sql with multiple where clause

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;

All Replies
Super User
Posts: 8,127

## Re: Sql with multiple where clause

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

Solution
‎10-13-2011 09:59 AM
PROC Star
Posts: 8,169

## Re: Sql with multiple where clause

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;

Occasional Contributor
Posts: 19

## Re: Sql with multiple where clause

Thank you Art297. It works!

Super User
Posts: 10,787

## Re: Sql with multiple where clause

```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

🔒 This topic is solved and locked.