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...
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.