topic Re: check for unique values of same account in SAS Procedures

check for unique values of same account

podarum — Mon, 25 Feb 2013 14:48:20 GMT

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

002 A

002 B

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

Re: check for unique values of same account

Reeza — Mon, 25 Feb 2013 15:41:52 GMT

Use the nodupkey option in proc sort.

proc sort data=have nodupkey;

by acct field_a;

run;

Re: check for unique values of same account

TomKari — Mon, 25 Feb 2013 15:46:31 GMT

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

Re: check for unique values of same account

Peter_C — Mon, 25 Feb 2013 17:31:09 GMT

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 ;

Re: check for unique values of same account

Reeza — Mon, 25 Feb 2013 17:36:28 GMT

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

002 A

002 B

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;

Re: check for unique values of same account

DBailey — Mon, 25 Feb 2013 17:41:18 GMT

proc sql;

create table want as

select acct

from have

group by acct

having count(distinct field_a)>1;

quit;

Re: check for unique values of same account

podarum — Mon, 25 Feb 2013 18:49:16 GMT

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