topic Re: Check for identical values. in SAS Procedures

Check for identical values.

attjooo — Tue, 15 Jan 2013 14:30:20 GMT

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

Re: Check for identical values.

Linlin — Tue, 15 Jan 2013 14:35:10 GMT

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;

Re: Check for identical values.

NagendraKumarK — Tue, 15 Jan 2013 15:10:16 GMT

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;

Re: Check for identical values.

joehinson — Tue, 15 Jan 2013 15:10:40 GMT

....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 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;