topic Re: SAS programming - Remove duplicates in SAS Programming

SAS programming - Remove duplicates

Pranshu — Tue, 08 May 2018 09:23:25 GMT

Hi All,

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

Source	Destination	Distance
USA	UK	1000
USA	Spain	200
UK	USA	1000
Germany	Spain	500
Spain	USA	200

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.

Source	Destination	Distance
USA	UK	1000
USA	Spain	200
Germany	Spain	500

Re: SAS programming - Remove duplicates

tomrvincent — Tue, 08 May 2018 12:03:25 GMT

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

Re: SAS programming - Remove duplicates

Ksharp — Tue, 08 May 2018 12:39:36 GMT

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;

Re: SAS programming - Remove duplicates

Tom — Tue, 08 May 2018 12:53:11 GMT

Why not just take those where Source < Destination?

Re: SAS programming - Remove duplicates

Ksharp — Tue, 08 May 2018 13:13:49 GMT

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

Re: SAS programming - Remove duplicates

Tom — Tue, 08 May 2018 13:23:41 GMT

Add
call sortc(source,destination);
Before IF statement.

Re: SAS programming - Remove duplicates

Pranshu — Wed, 09 May 2018 05:23:58 GMT

Thanks for your response. This really solved my query.