SAS programming - Remove duplicates

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

SAS programming - Remove duplicates

Hi All,

 

I have the following data based on distance between the cities.

 

SourceDestinationDistance
USAUK1000
USASpain200
UKUSA1000
GermanySpain500
SpainUSA200

 

I want to remove the duplicates where source and destination are same. For Example USA to UK will be same as UK to USA and hence the duplicate value needs to be removed.

 

Following is the desired output.

 

SourceDestinationDistance
USAUK1000
USASpain200
GermanySpain500

 

 


Accepted Solutions
Solution
‎05-09-2018 01:22 AM
Super User
Posts: 10,784

Re: SAS programming - Remove duplicates

data have;
infile cards expandtabs;
input Source $ Destination $ Distance;
cards;
USA	UK	1000
USA	Spain	200
UK	USA	1000
Germany	Spain	500
Spain	USA	200
;
run;
data temp;
 set have;
 call sortc(Source,Destination);
run;
proc sort data=temp out=want nodupkey;
by Source Destination;
run;
proc print;run;

View solution in original post


All Replies
Regular Contributor
Posts: 217

Re: SAS programming - Remove duplicates

concatenate source and destination in alphabetical order and then pick max distance for each pair.

Solution
‎05-09-2018 01:22 AM
Super User
Posts: 10,784

Re: SAS programming - Remove duplicates

data have;
infile cards expandtabs;
input Source $ Destination $ Distance;
cards;
USA	UK	1000
USA	Spain	200
UK	USA	1000
Germany	Spain	500
Spain	USA	200
;
run;
data temp;
 set have;
 call sortc(Source,Destination);
run;
proc sort data=temp out=want nodupkey;
by Source Destination;
run;
proc print;run;
New Contributor
Posts: 2

Re: SAS programming - Remove duplicates

Thanks for your response. This really solved my query.

 

Super User
Super User
Posts: 8,114

Re: SAS programming - Remove duplicates

Why not just take those where Source < Destination?

 

Super User
Posts: 10,784

Re: SAS programming - Remove duplicates

If there is only one obs, and Source > Destination?

Super User
Super User
Posts: 8,114

Re: SAS programming - Remove duplicates

Add
call sortc(source,destination);
Before IF statement.
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 195 views
  • 0 likes
  • 4 in conversation