Date:         Wed, 31 Aug 2005 08:49:46 -0700
Reply-To:     "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Subject:      Re: Count occurances
Comments: To: Alex Pavluck <apavluck@GMAIL.COM>
Content-Type: text/plain; charset="US-ASCII"

Hi Alex,

Looks perfectly fine!

With a 3192MHz box is 12seconds still a problem?
(your numbers may be different of course)





data test;
    input DISTRICT EVENT;
cards;
1          111
1          111
1          234
1          355
1          422
1          422
1          234
2          111
;
run;
proc sql;
   create table CNT as
   select DISTRICT,
          count(distinct EVENT) AS EVENT_COUNT
   from TEST
   group by DISTRICT;
quit;



    * two sets to tally ;
data make800k;
    set test;
    do i = 1 to 100000;
        output;
    end;
run;
proc sql;
   create table CNT as
   select DISTRICT,
          count(distinct EVENT) AS EVENT_COUNT
   from make800k
   group by DISTRICT;
quit; * 2.20sec, 2.35sec, 2.23sec ;



    * 200,000 sets to tally ;
data make800k;
    set test;
    do i = 1 to 100000;
        DISTRICT = DISTRICT + i;
        output;
    end;
run;
proc sql;
   create table CNT as
   select DISTRICT,
          count(distinct EVENT) AS EVENT_COUNT
   from make800k
   group by DISTRICT;
quit; * 12.70sec, 12.15sec, 12.20sec ;




Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group


Russell
Global Leaders in Multi-Manager Investing









-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Alex Pavluck
Sent: Wednesday, August 31, 2005 8:25 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Count occurances


I have a dataset that has 800,000 observations.

DISTRICT  EVENT
1          111
1          111
1          234
1          355
1          422
1          422
1          234
2          111


I want this:
District   EVENT_COUNT
1           4
2           1



I am using this but I am not sure if it is right or the best method:

proc sql;
   create table CNT
   as
   select DISTRICT,
          count(distinct EVENT) AS EVENT_COUNT
   from TEST
   group by DISTRICT;
   quit;

proc print data=TEST;run;