BookmarkSubscribeRSS Feed
attjooo
Calcite | Level 5

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

3 REPLIES 3
Linlin
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 746 views
  • 0 likes
  • 4 in conversation