topic Re: Sql with multiple where clause in SAS Procedures

Sql with multiple where clause

HDSimo — Thu, 13 Oct 2011 13:45:33 GMT

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

Re: Sql with multiple where clause

Tom — Thu, 13 Oct 2011 13:57:39 GMT

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

Re: Sql with multiple where clause

art297 — Thu, 13 Oct 2011 13:59:39 GMT

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;

Re: Sql with multiple where clause

HDSimo — Thu, 13 Oct 2011 14:08:23 GMT

Thank you Art297. It works!

Re: Sql with multiple where clause

Ksharp — Tue, 18 Oct 2011 01:57:04 GMT

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