How to identify unordered pairs

[prasunb]

I am new to this forum...need some real help since I am frustrated with this problem for a while now...I am not familiar with any formal programming language (like C) and only have some knowledge of working with SAS and STATA. I have this airport origin and destination survey data....There are origin and destination airports listed by itineraries. I want to have a dataset out of this where I can have a listing of airport pairs without caring about the order. Thus to give you some idea the data set I have looks like

ItID Origin Destination
1 ABQ LAX
2 ORD ABQ
3 LAX ABQ
4 IAD ORD

So in the final listing I only for example need either the pair ABQ-LAX and not the pair LAX-ABQ....i.e. they should get the same identification number....someone told me this can be done using some loop in SAS but he couldn't help me out....any help from you guys would be greatly appreciated..

 

[ChrisW75]

Hmmm. A toughy this one. But I think I can see a way.
First up, as you don't care which direction the travel is we can lose that information.

data all(drop=origin destination);
  set itineries;

  airport = origin;
  output;
  airport = destination;
  output;
run;

This should give you:-
ItID Airport
1 ABQ
1 LAX
2 ORD
2 ABQ
3 LAX
3 ABQ
4 IAD
4 ORD

Now, if we sort this by Itinerary ID and Aiprot code, it should get every trip in the same order;

proc sort data=all;
  by itID airport;
run;

Now you want to transpose this data, either using proc transpose or manually in a datastep.

data all_tr;
  set all;
  by itID;
  if first.itID then airport1 = airport;
  else if last.itID then
  do;
    airport2 = airport;
    output;
  end;
run;

Now you should have your journeys all in the same direction. Now all you need to do is dedupe by Airport1 and Airport2;

proc sort data=all_tr
           out=all_tr_dedupe
           nodupkey;
  by airport1 airport2;
run;

That should pretty much do it I think. It could possibly be done in a more elegant fashion, but I prefer heavy duty code that's asy to pull apart to elegant stuff that needs to be re-written from the ground up if you want to make a small change.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.