Date:         Thu, 30 Nov 2006 22:49:04 -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: count within group

On Wed, 29 Nov 2006 09:12:35 -0800, T. C. <cychen9@GMAIL.COM> wrote:

>Hello,
>I have a data set with 2 columns (group and id).
>How could I count how many id showed up greater than once within
>groups?
>
>Thanks ^o^
>
>group    id   count
>-----   ----  -----
>1        A      2
>1        B
>1        B
>1        C
>1        C
>2        A      1
>2        A
>2        B
>2        C
>2        D
>3        A      2
>3        A
>3        B
>3        C
>3        C
>.
>.
>.

There are several ways. Here are two.

Load data:

data have;
input
group    id $;      cards;
1        A      2
1        B
1        B
1        C
1        C
2        A      1
2        A
2        B
2        C
2        D
3        A      2
3        A
3        B
3        C
3        C
   ;

PROCC SQL can do it in one statement:

   proc sql;
   select group, count(*) as count
    from (select *
           from have
           group by group, id
           having count(*)>1
         )
    group by group
   ;
   quit;

Result:

   group     count
   ---------------
       1         2
       2         1
       3         2

With other PROCs it takes two steps, as with:

   proc summary data=have nway;
   class group id;
   output out=halfbaked;
   run;

   proc freq data=halfbaked;
   where _freq_>1;
   table group / nocum nopercent;
   run;

Result:

   group    Frequency
   ------------------
       1           2
       2           1
       3           2