Help using Base SAS procedures

Sql with multiple where clause

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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: 7,357

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;

View solution in original post


All Replies
Super User
Super User
Posts: 6,498

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: 7,357

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: 9,671

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 125 views
  • 0 likes
  • 4 in conversation