Date:         Thu, 9 Oct 2003 10:47:30 -0400
Reply-To:     Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Subject:      Re: outputing observations that appear a specific number of times

Tomislav's problem was easy to tackle because he provided a ready-to-run
test dataset. That's a very good practice.

I would streamline Harry's code a bit:

   create table blah as
    select * from temp
     group by a, b
     having count(*) >= &lmt
   ;

It's also possible to do the same thing in one double-pass DATA step:

   data blah_again;
   set temp(in=preview)
       temp(in=process);
   by a b;
   if first.b then count = 0; drop count;
   if preview then count ++ 1;
   if process and count >= &lmt then output;
   run;

On Thu, 9 Oct 2003 08:49:13 -0400, Droogendyk, Harry
<Harry.Droogendyk@CIBC.COM> wrote:

>SQL solution, cut-off determined by macro variable &lmt:
>
>%let lmt = 2;
>proc sql;
>  create table blah ( drop = _: ) as
>    select a, b, c, temp, '1' as flag, count(*) as _cnt
>      from temp
>     group by a, b
>    having _cnt >= &lmt
>    ;
>quit;
>
>proc print;
>run;
>
>                -----Original Message-----
>                From:   Tomislav Svoboda [mailto:tomislavtemp@HOTMAIL.COM]
>                Sent:   Thursday, October 09, 2003 8:29 AM
>                To:     SAS-L@LISTSERV.UGA.EDU
>                Subject:        outputing observations that appear a
>specific number of times
>
>                Hi all,
>                I wrote code that outputs all observations or records that
>appear
>                twice. E.g. let's say you want to extract all records (e.g.
>with a key
>                of 2 variables) that appear 2 or more times. I'm wondering
>if there is
>                a better way of doing this (I bet SQL) or a more
>generalizable method
>                that can output those observations appearing any number of
>times e.g.
>                3 or (4 or more) etc...
>                thanks
>                Tomislav
>
>                data temp;
>                input a  b  c temp;
>                cards;
>                1       12      3       1
>                1       12      4       1
>                1       12      5       1
>                1       13      6       1
>                1       14      5       1
>                1       14      6       1
>                2       22      3       1
>                3       32      5       1
>                3       32      6       1
>                3       32      8       1
>                ;
>                run;
>                proc sort;by a b;run;
>                data temp2;
>                        set temp;
>                        temp = 2;
>                run;
>                proc sort nodupkey; by a b;run;
>                data temp3;
>                        merge temp      temp2;
>                        by a b;
>                        if temp = 1;
>                        flag = 1;
>                run;
>                data temp4;
>                        merge temp temp3;
>                        by a b;
>                        if flag = 1;
>                run;
>                proc print;run;