topic Re: Creating a to-from list from a vector in SAS Programming

Creating a to-from list from a vector

ulricius — Mon, 09 Mar 2020 12:55:47 GMT

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-from-external/m-p/627816 .

/Ulrik

Re: Creating a to-from list from a vector

RichardDeVen — Mon, 09 Mar 2020 14:13:21 GMT

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

Re: Creating a to-from list from a vector

FreelanceReinh — Mon, 09 Mar 2020 17:44:11 GMT

@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-from-external/m-p/627816 .

/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;

Re: Creating a to-from list from a vector

ulricius — Wed, 11 Mar 2020 22:06:15 GMT

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!