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
Use the nodupkey option in proc sort.
proc sort data=have nodupkey;
by acct field_a;
run;
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
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 ;
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;
proc sql;
create table want as
select acct
from have
group by acct
having count(distinct field_a)>1;
quit;
Thanks guys.. These are all helpful and working examples...
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.