Check for identical values.

[attjooo]

I have a table with columns ID and TRANS_DATE. Each ID can have many records. I want to know if any ID have any records with the same TRANS_DATE.

ID	TRANS_DATE
1	2012-07-01
1	2010-08-07
1	2012-07-01
2	2010-10-01
2	2010-10-01
2	2011-06-01
2	2011-06-01
3	2012-07-05
3	2012-09-07
3	2012-05-09

The result should be:

ID	TRANS_DATE
1	2012-07-01
2	2010-01-01
2	2011-06-01

How can I get this result?

/attjooo

[Linlin]

proc sort data=your_data;

  by id trans_date;

run;

data new_data;

  set your_data

by id trans_date;

if first.trans_date and last.trans_date then delete;

run;

[NagendraKumarK]

data hello; infile datalines; input cid date yymmdd12.; format date ddmmyy10.; datalines; 1 2012-07-01 1 2010-08-01 1 2012-07-01 2 2010-10-01 2 2010-10-01 2 2011-06-01 2 2011-06-01 3 2012-07-05 3 2012-09-07 3 2012-05-09 ; run; proc sql; create table shello as ( select distinct cid, date,count(date) as cnt from hello group by cid,date having count(date)>1 ); run; proc print data=shello; run;

[joehinson]

....and in case you wish to avoid sorting:

data given;

input  ID TRANS_DATE : yymmdd10.;

datalines;

1 2012-07-01

1 2010-08-07

1 2012-07-01

2 2010-10-01

2 2010-10-01

2 2011-06-01

2 2011-06-01

3 2012-07-05

3 2012-09-07

3 2012-05-09

;

run;

data solved(drop=rc);

     if(1=2) then set given;

     declare hash sv(ordered:"a");

     sv.defineKey("id","trans_date");

     sv.defineDone();

     do until(done);

          set given end=done;

          rc=sv.check();

          if rc=0 then output;

          rc=sv.replace();

     end;

     format trans_date yymmdd10.;

     stop;

run;