Date:         Thu, 21 Dec 2006 10:48:01 -0500
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: PROC SQL help!

On Thu, 21 Dec 2006 10:18:24 -0500, Jack Clark <JClark@CHPDM.UMBC.EDU> wrote:

>Yun,
>
>
>data have;
>input a $ b $ c $ d;
>cards;
>111 qqq ppp  1
>111 qqq ooo 1
>111 qqq lll 0
>222 www ppp 0
>222 www ooo 1
>222 www lll 0
>;
>run;
>
>
>proc sql;
>   create table need as
>   select a, b, sum(case when d=1 then 1
>                       else 0
>                                    end) as d1,
>                                sum(case when d=0 then 1
>                                       else 0
>                                    end) as d0
>   from have
>   group by a, b
>   ;
>quit;
>
>proc print data = need;
>run;
>

Or just

   select a, b, sum(d=1) label='d=1', sum(d=0) label='d=0'
    from have
    group by 1, b;

Output:

   a         b              d=1       d=0
   --------------------------------------
   111       qqq              2         1
   222       www              1         2

>
>HTH
>
>Jack Clark
>Research Analyst
>Center for Health Program Development and Management
>University of Maryland, Baltimore County
>
>
>
>-----Original Message-----
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Yun Bai
>Sent: Thursday, December 21, 2006 10:01 AM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: PROC SQL help!
>
>Hi,
>
>I have a data table like this:
>
>a b c d
>---------------------------
>111 qqq ppp  1
>111 qqq ooo 1
>111 qqq lll 0
>222 www ppp 0
>222 www ooo 1
>222 www lll 0
>.......
>
>How can I use PROC SQL to generate a table to count how many rows have d=1
>and d=0 for distinct group? Like this:
>
>a b d=1 d=0
>----------------------------
>111 qqq 2 1
>222 www 1 2
>......
>
>Thank you!