SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Removal of duplicates at transactional level

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

Removal of duplicates at transactional level

data a;
input remove_dt ac_no reason$ cl_tid;
informat remove_dt ddmmyy8.;
format remove_dt ddmmyy8.;
cards;
1/4/2014 1111 A 222
1/4/2014 1111 A 222
1/4/2014 1111 T 222 
2/5/2014 5555 B 666
4/5/2014 4444 C 222
2/5/2014 5555 D 666
1/4/2014 1111 R 333
2/5/2014 5555 B 666
2/5/2014 5555 B 666
2/5/2014 5555 G 666

run; 

1.) Out of the above dataset named a, i want all the duplicate records with duplicate remove_dt, ac_no, reason and cl_tid at transaction level..
So the ouput should be-
  remove_dt ac_no reason cl_tid
1/4/2014 1111 A 222 
1/4/2014 1111 A 222
2/5/2014 5555 B 666
2/5/2014 5555 B 666
2/5/2014 5555 B 666

2.) Out of the dataset named a, i want the duplicate records with remove_dt, ac_no, cl_tid
So the output should be-

remove_dt ac_no reason cl_tid
1/4/2014 1111 A 222 
1/4/2014 1111 A 222
1/4/2014 1111 T 222
2/5/2014 5555 B 666
2/5/2014 5555 D 666
2/5/2014 5555 B 666 
2/5/2014 5555 B 666
2/5/2014 5555 G 666 

Now, out of the above dataset, i want a cross tab of accounts by cl_tid and reason!


Accepted Solutions
Solution
‎01-10-2015 02:18 PM
Super User
Posts: 19,864

Re: Removal of duplicates at transactional level

Posted in reply to venkatnaveen

It looks like you want to keep records with multiple occurrences rather than remove duplicates?

Look at proc sort with variants on the NOUNIQUEKEY option. You may want to consider the order of your BY variables, my guess is that ac_no should be first.

proc sort data=have out=want nouniquekey;

by ac_no remove_dt cl_tid;

run;

View solution in original post


All Replies
Solution
‎01-10-2015 02:18 PM
Super User
Posts: 19,864

Re: Removal of duplicates at transactional level

Posted in reply to venkatnaveen

It looks like you want to keep records with multiple occurrences rather than remove duplicates?

Look at proc sort with variants on the NOUNIQUEKEY option. You may want to consider the order of your BY variables, my guess is that ac_no should be first.

proc sort data=have out=want nouniquekey;

by ac_no remove_dt cl_tid;

run;

Frequent Contributor
Posts: 89

Re: Removal of duplicates at transactional level

does it work on SAS 9.2 version

Super User
Posts: 19,864

Re: Removal of duplicates at transactional level

Posted in reply to venkatnaveen

No, only SAS 9.3+. 

Instead you'll need to use first/last logic. I've changed my mind about the order of the by statements as well.

Assuming sort:

proc sort data=have; by ac_no cl_tid remove_dt;

data want;

set have;

by ac_no cl_tid remove_dt;

if not (first.remove_dt and last.remove_dt);

run;

Frequent Contributor
Posts: 115

Re: Removal of duplicates at transactional level

Posted in reply to venkatnaveen

Sorry, Your question is not quite clear or my understanding is poor. I can only agree with Reeza on looking at your data. Can you explain to Reeza's point "It looks like you want to keep records with multiple occurrences rather than remove duplicates?" .

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 321 views
  • 4 likes
  • 3 in conversation