Date:         Fri, 22 Apr 2005 16:18:36 -0400
Reply-To:     Jeff Abolafia <jabolafi@RHOWORLD.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jeff Abolafia <jabolafi@RHOWORLD.COM>
Subject:      Re: SQL grouping question

On Fri, 22 Apr 2005 18:31:04 GMT, Rusty Shackleford <rs@NATCHIE.MINE.NU> wrote:

>I have two tables.  Table mem has one row per ID.  Table claims has many
>rows per ID.  I want to add a var to mem that says for each ID, how many
>claims exist.  Is there a way to do this one proc sql statement?
>
>This is what I have now, and it works OK:
>
>    proc summary data=claims nway;
>        class id;
>        output = csum (keep=id _FREQ_ rename=(_FREQ_=idcount));
>    run;
>
>    proc sql;
>        create table m1 as
>        select m.id, c.idcount
>        from mem m left join csum c
>        where m.id = c.id;
>    quit;
>
>
>
>TIA

/* dataset onerec has 1 record/id.  dataset many has multiple records/id  */
proc sql ;
  create table all as
  select a.id,count(b.id) as ns
  from onerec as a left join many as b
  on a.id=b.id
  group by a.id
  ;
quit ;