Date:         Fri, 1 Jul 2005 09:05:15 -0700
Reply-To:     "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Subject:      Re: Identifying faults in combined primary keys
Comments: To: Rune Runnestø <rune@FASTLANE.NO>
Content-Type: text/plain; charset="iso-8859-1"

Ahhh,  if your last line is what
you are after, any code with more
than one description, then adjusting
your original attempt with pre-creating
unique values with a nested query to get
the distinct values and then count 'em.
Which will work for as many as you have.

data myds;
    attrib  code    length=$20
            codevalue     length=$12;
    infile cards;
    input   @1 code $20.
            @21 codevalue $12. ;
datalines;
AA                  two A
AA                  two A
BB                  two B
BB                  two B
AB                  oneAandoneB
BA                  oneBandoneA
AA                  two A
AA                  three A
;
run;

proc sql;
    create table codeerrors as
    select
          code,
          codevalue,
          count(code||codevalue) as num_records
    from
         (select distinct code, codevalue
          from myds)
    group by code
    having count(*) > 1;
run;





Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group


Russell
Global Leaders in Multi-Manager Investing






-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Rune Runnestø
Sent: Friday, July 01, 2005 8:55 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Identifying faults in combined primary keys


2 is not the expected ceiling for all pairs including the mixed pairs. This example was just made for simplicity. In practice, there might be for example up to ten characters in the CODE value.

MYDS is just an examples, in practice, there are thousands of records, and there is no way possible to hardcode the right  values that can appear in the column CODEVALUE either.

The only criteria is that given one value in CODE, the SAS-code must catch the records with different values in CODEVALUE.

Regards
Rune