Date:         Tue, 14 Apr 2009 20:44:24 -0400
Reply-To:     msz03@albany.edu
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Zdeb <msz03@ALBANY.EDU>
Subject:      Re: Checking for repeated values vertically...
Content-Type: text/plain;charset=iso-8859-1

hi ... you get a little bit more info than you asked about, but you could try ...

data x;
input id visit b1 : $1. (b2-b4) (: $2.);
obs = _n_;
datalines;
       1       1       a       a1      a3      a4
       1       2       b       a2      b3      b4
       1       3       c       a3      c3      c4
       1       4       d       a4      d3      d4
       1       5       a       a5      e3      e4
       1       6       e       a2      f3      f4
       2       1       f       b2      g3      g4
       2       2       g       c2      h3      h4
       2       3       h       d2      i3      i4
       2       4       d       e2      j3      j4
       2       5       i       f2      k3      k4
       2       6       j       g2      l3      l4
;
run;

proc sql;
select obs, b1 from x where b1 in (select b1 from x group by b1 having count(*) gt 1 );
select obs, b2 from x where b2 in (select b2 from x group by b2 having count(*) gt 1 );
select obs, b3 from x where b3 in (select b3 from x group by b3 having count(*) gt 1 );
select obs, b4 from x where b4 in (select b4 from x group by b4 having count(*) gt 1 );
quit;


you could also put them in data sets by adding 'create table b1 as ...', etc.

--
Mike Zdeb
U@Albany School of Public Health
One University Place
Rensselaer, New York 12144-3456
P/518-402-6479 F/630-604-1475

> Hi All,
>
> I am trying to find a logic for this edit check:
>
> "Bottle Numbers should not be duplicated across all visits "
> Is there a way we can show the visit and where it's duplicated? For
> example is bottle number 38568 is duplicated I want to know for what
> subject and what visit, For example Subject 182334043 week 4 Bottle
> number 38568 and 182658905 week 12 Bottle number 38568 (this is just a
> made up example by the way)
>
>
>
>         usubjid visit   bottle1 bottle2 bottle3 bottle4
>
> 1       1       1       a       a1      a3      a4
> 2       1       2       b       a2      b3      b4
> 3       1       3       c       a3      c3      c4
> 4       1       4       d       a4      d3      d4
> 5       1       5       a       a5      e3      e4
> 6       1       6       e       a2      f3      f4
> 7       2       1       f       b2      g3      g4
> 8       2       2       g       c2      h3      h4
> 9       2       3       h       d2      i3      i4
> 10      2       4       d       e2      j3      j4
> 11      2       5       i       f2      k3      k4
> 12      2       6       j       g2      l3      l4
>
>
>
> I want to output records which has duplicate bottle number across
> visits.
> I need to o/p:
>
>  Record 5  -- bottle 1 ('a') is repeating,
>  Record 6  -- bottle 2 ('a2') is repeating..
>  Record 10 -- bottle 1 ('d')  is repeating
>
> Thanks a lot!!!!
>
> Z
>