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
/Ulrik
@ulricius wrote:(...)
This is a simplified version of a bigger issue, discussed at lenght in
/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;
Can you explain the expansion for ID=1 in more detail ?
@ulricius wrote:(...)
This is a simplified version of a bigger issue, discussed at lenght in
/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;
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.