topic Re: Removing row duplicates with value in diffrent variable in SAS Procedures

Removing row duplicates with value in diffrent variable

naveen20jan — Mon, 15 Dec 2014 13:22:10 GMT

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

for the help in advance

Re: Removing row duplicates with value in diffrent variable

RW9 — Mon, 15 Dec 2014 13:46:38 GMT

How about:

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;

Re: Removing row duplicates with value in diffrent variable

Ksharp — Mon, 15 Dec 2014 14:54:42 GMT

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

Re: Removing row duplicates with value in diffrent variable

Haikuo — Mon, 15 Dec 2014 19:59:19 GMT

If your incoming data:

1. already have duplicates

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

Re: Removing row duplicates with value in diffrent variable

naveen20jan — Tue, 16 Dec 2014 07:01:58 GMT

Hi Xai ,

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

thanks

Re: Removing row duplicates with value in diffrent variable

naveen20jan — Tue, 16 Dec 2014 07:02:31 GMT

Thanks RW9