BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
naveen20jan
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Ksharp
Super User

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

naveen20jan
Obsidian | Level 7

Hi Xai ,

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

thanks

Haikuo
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 964 views
  • 5 likes
  • 4 in conversation