Date:         Tue, 15 Sep 2009 15:44:04 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Summay Data by Proc Sql --- Easy Question. I think my braind
              dies              today
Comments: To: pigpigpig <pigzhu740@GMAIL.COM>
In-Reply-To:  <eb7ec5e7-64c5-47cb-946e-1f0dd1fc30da@g23g2000vbr.googlegroups.com>
Content-Type: text/plain; charset="utf-8"

I'd say an easy question if you use PROC FREQ and know the correct parameter values, but not so easy in SAS SQL:

PROC SQL;
    select distinct t1.ID as ID,t1.TYPE as TYPE, coalesce(t2.CNT,0) as CNT
        from (select * from (select distinct ID from aa),(select distinct TYPE from aa)) as t1
    left join
    (SELECT distinct ID,TYPE,COUNT(ID) as CNT
     from aa
     GROUP BY ID,TYPE
         ) as t2
         on t1.ID=t2.ID and t1.TYPE=t2.TYPE
         ;
 QUIT;

S

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of pigpigpig
Sent: Tuesday, September 15, 2009 2:12 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Summay Data by Proc Sql --- Easy Question. I think my braind dies today

I think it should be a easy question. My brain dies today.
I have a dataset as follow:

There are totally 2 claim types.

Data aa;
input  ID $2. +1 TYPE $3.;
cards;
99 LTR
99 LTR
99 STR
00 STR
00 STR
00 STR
;
RUN;

PROC PRINT DATA=aa;
run;

PROC SQL;
SELECT ID, TYPE, COUNT(ID) as CNT from aa
GROUP BY ID, TYPE;
QUIT;
run;

       The SAS System                    09:20 Tuesday, September 15,
2009   4

                                                      ID  TYPE
CNT

ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
                                                      00  STR
3
                                                      99  LTR
2
                                                      99  STR
1

ID=00 doesn't have any claim type=LTR,
Is there a quick way to let the output also shows
                                                       00
LTR           0
                                                      00  STR
3
                                                      99
LTR           2
                                                      99  STR
1

By the way, I am hoping this can be done in one sql. I don't want
extra merge step or data step.