Solved
Contributor
Posts: 30

# Removing row duplicates with value in diffrent variable

Hi All ,

I have the below dataset

data x;

input source \$3. fare  destination \$3.;

datalines;

mum 500 del

del 500 mum

kol 600 che

che 600 kol

;

run;

i want only one oservation for a source and destination ie one obs for to and fro journey for ex   out of  del and mum and mum to del i need only one, same as for others .

the output should be like this

mum 500 del

kol 600 che

thanks

Accepted Solutions
Solution
‎12-15-2014 08:46 AM
Super User
Posts: 9,599

## Re: Removing row duplicates with value in diffrent variable

data want;
set x;
length forwards backwards \$200.;
forwards=catx(',',source,destination);
backwards=catx(',',destination,source);
if forwards=lag(forwards) or forwards=lag(backwards) then delete;
run;

All Replies
Solution
‎12-15-2014 08:46 AM
Super User
Posts: 9,599

## Re: Removing row duplicates with value in diffrent variable

data want;
set x;
length forwards backwards \$200.;
forwards=catx(',',source,destination);
backwards=catx(',',destination,source);
if forwards=lag(forwards) or forwards=lag(backwards) then delete;
run;

Contributor
Posts: 30

Thanks RW9

Super User
Posts: 10,761

## Re: Removing row duplicates with value in diffrent variable

Is there some order you need to consider ?

```data x;
input source \$3. fare  destination \$3.;
datalines;
mum 500 del
del 500 mum
kol 600 che
che 600 kol
;
run;
data x;
set x;
s=source;
d=destination;
call sortc(s,d);
run;
proc sort data=x out=want nodupkey;by s d;run;

```

Xia Keshan

Contributor
Posts: 30

## Re: Removing row duplicates with value in diffrent variable

Hi Xai ,

thanks for the help and its fine we dont need any order .

thanks

Posts: 3,167

## Re: Removing row duplicates with value in diffrent variable

2. same pair of "from-to" do not cluster together (could be any where in the table),

Then to use the solution by , or the following:

data x;

input source \$3. fare  destination \$3.;

datalines;

mum 500 del

del 500 mum

kol 600 che

che 600 kol

;

run;

proc sql;

create table want (drop=grp n)  as

select *, ifc(source <= destination, cats(source, destination), cats(destination,source)) as grp, monotonic() as n from x

group by grp

having n=min(n)

;

quit;

Haikuo

🔒 This topic is solved and locked.