check for unique values of same account

[podarum]

Hi,  Is there a way I can check if the values of a field (eg. Field_A) is unique for duplicated accounts.  So for example I have a datset with many accounts, at least 2 records belong to the same account, and Field_A should also be unique, but in the cases where Field_A has 1 value for one record and another value for the other record.. and to top it all off, there may be more than 2 records for each account..

HAVE:

Acct     Field_A

001          A

001          A

002          A

002          B

003          A

003          A

003          B

003          A

I would like to find out which accounts have both a Field_A =  A and a B.

WANT:

Acct     Field_A

002        A

002        B

003        A

003        B

Thanks

[Reeza]

Use the nodupkey option in proc sort.

proc sort data=have nodupkey;

by acct field_a;

run;

[TomKari]

There are a couple of things that I find unclear, but this should start to flush out the issues.

proc sql;
create table summ as
  select acct, fld_a, count(*) as count_var from have
   group by acct, fld_a order by acct, fld_a;
quit;

data want;
set summ;
by acct;

if ^(first.acct & last.acct);
drop count_var;
run;

Tom

[Peter_C]

interesting challenge:

Has an account at least one row with A and one row with B in column Field_A  ?

Assuming the data can be read in ACCT order, this should doL

data accts_a_and_b ;

  do until( last.acct ) ;

    set that.data ;

    by acct ;

    a_ct + ( field_a ='A' ) ;

    b_ct + ( field_a ='B' ) ;

  end ;

  if a_ct and b_ct then output ;

  call missing( a_ct, b_ct ) ;

  keep acct ;

run ;

[Reeza]

Wrong answer first time. Here's a sql one step solution, but it does do 2 passes through the data:

data have;

input acct $ field_a $;

cards;

001 A

001 A

002 A

002 B

003 A

003 A

003 B

003 A

;

proc sql;

    create table want as

    select distinct acct, field_a

    from have

    group by acct

    having max(field_a) ne min(field_a);

quit;

[DBailey]

proc sql;

create table want as

select acct

from have

group by acct

having count(distinct field_a)>1;

quit;

[podarum]

Thanks guys.. These are all helpful and working examples...