Help using Base SAS procedures

Check for identical values.

Reply
Contributor
Posts: 30

Check for identical values.

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.

IDTRANS_DATE
12012-07-01
12010-08-07
12012-07-01
22010-10-01
22010-10-01
22011-06-01
22011-06-01
32012-07-05
32012-09-07
32012-05-09

The result should be:

IDTRANS_DATE
12012-07-01
22010-01-01
22011-06-01

How can I get this result?

/attjooo

Super Contributor
Posts: 1,636

Re: Check for identical values.

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;

Occasional Contributor
Posts: 14

Re: Check for identical values.

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;

Contributor
Posts: 45

Re: Check for identical values.

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

Ask a Question
Discussion stats
  • 3 replies
  • 154 views
  • 0 likes
  • 4 in conversation