Creating a to-from list from a vector

[ulricius]

Hi,

I would like to create a list with the following principal confuguration,

LineID;FromStopID;ToStopID
1;1;1
1;2;1
1;2;2
1;2;3
1;3;1
1;3;3
2;4;4
2;5;4
2;5;5
2;5;6
2;6;4
2;6;6

that is, a list of all possible pairwise combinations of entries from a dataset looking like this (the third column is what I regard as the "stopID vector"):

LineID;SequenceID;StopID
1;1;2
1;2;3
1;3;1
2;1;5
2;2;6
2;3;4

How should I proceed with the coding? As some of the combinations are missing in the inut table, they may me left out. The important requirement is that the code should only result in a list of those "ToStopID"s that follow after each "FromStopID" (=a higher sequenceID) in the input table.

This is a simplified version of a bigger issue, discussed at lenght in

https://communities.sas.com/t5/SAS-Programming/Flaging-closest-match-per-row-by-referencing-data-fro... .

/Ulrik

[RichardDeVen]

Can you explain the expansion for ID=1 in more detail ?

[FreelanceReinh · Accepted solution]

---

@ulricius wrote:

(...)

This is a simplified version of a bigger issue, discussed at lenght in

https://communities.sas.com/t5/SAS-Programming/Flaging-closest-match-per-row-by-referencing-data-fro... .

/Ulrik

---

Hi @ulricius,

Without delving into that lengthy other thread I'd suggest this PROC SQL solution:

data have;
input LineID SequenceID StopID;
cards;
1 1 2
1 2 3
1 3 1
2 1 5
2 2 6
2 3 4
;

proc sql;
create table want as
select f.LineID, f.StopID as FromStopID, t.StopID as ToStopID
from have f, have t
where f.LineID=t.LineID & f.SequenceID<=t.SequenceID
order by 1,2,3;
quit;

[ulricius]

Perfect! Thanks @FreelanceReinh. Tried it on the full dataset now (1.5 million rows), and after a ten-hour run, and subsequent NODUPKey run, I got it just as I wanted it! Brilliant!