Date:         Sun, 26 Oct 2003 20:23:05 -0800
Reply-To:     "Huang, Ya" <yhuang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <yhuang@AMYLIN.COM>
Subject:      Re: SQL Problem
Comments: To: Paul <reganmian@YAHOO.COM>
Content-Type: text/plain; charset="iso-8859-1"

data xx;
input a b $;
CARDS;
2    I210
3    I210
2    I210
6    I210
7    I210
8    I210
3    I210
2    I210
2    I950
2    I950
3    I950
2    I950
6    I950
2    I950
;

proc sql;
select distinct a, b, count(*)
from xx
group by a, b
order by b, a
;
----------
      a  b
fffffffffffffffffffffffffff
      2  I210             3
      3  I210             2
      6  I210             1
      7  I210             1
      8  I210             1
      2  I950             4
      3  I950             1
      6  I950             1

Kind regards,

Ya Huang


-----Original Message-----
From: Paul [mailto:reganmian@YAHOO.COM]
Sent: Sunday, October 26, 2003 6:19 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SQL Problem


Hi

Got cards
CARDS;
2    I210
3    I210
2    I210
6    I210
7    I210
8    I210
3    I210
2    I210
2    I950
2    I950
3    I950
2    I950
6    I950
2    I950

need to count distinct item no. and type, output to a dataset like
2    I210   3
3    I210   2
...
2    I950   4
...

tried the sql:
proc sql;
  create table ACCTNO  as
  select DISTINCT A, B, COUNT(DISTINCT A)
  from OUT GROUP BY A;
quit;

but seems not working

Can you help on this SQL, or other solutions?

Many thanks
Paul