SAS - select rows where unique ID occurs for first time (ID can be in two columns)

user_14693658 0
Viewed 90

I have the calculated distance (var1) between customers (ID_a and ID_b). Var2 and var3 contain some other variables. The data is sorted on ID_a and var1 (so the closest distance comes first).

data have;
input (ID_a ID_b var1 var2 var3)($);
cards;
    A B 12 xxx yyy
    A C 36 xxx yyy
    C D 17 xxx yyy
    D A 18 xxx yyy
    D F 80 xxx yyy
    G B 20 xxx yyy
    G K 32 xxx yyy

run;

Now I want to keep the rows where an ID occurs for the first time; whether it is in column ID_a or column ID_b. The output should look like this:

data want;
input (ID_a ID_b var1 var2 var3)($);
cards;
   A B 12 xxx yyy
   C D 17 xxx yyy
   G K 32 xxx yyy

run;

The goal is to get a list where every customer is paired to one (1) other customer, with the closest possible distance. Of course there are multiple possible combinations, because A can be the closest to B, while at the same time C can also be the closest (and even more close) to B. If possible it would be nice to get the combinations with the lowest total distance. And otherwise run through the combinations in ascending order of ID_a

What would be the best solution to do this? I have been checking on PROC SORT with NODUPKEY, but that is no solution because every row is a unique combination.

I am thinking about using a macro to select every first row of every ID_a, and if the ID already has been used in ID_a or ID_b then proceed to the second row of ID_a. However I couldn’t find any example of such a code and I’m not sure this is the best solution. So any suggestions for solutions or code would be nice. Thanks!

sas
1 Answers

Based on the comments I would recommend sorting the data by distance and selecting the first pair that appears containing unique pair items.

Example:

A hash is very effective for tracking selected items.

proc sort data=have;
  by var1 id_a;

data want;
  length id $8;
  if _n_ = 1 then do;
    declare hash items();
    items.defineKey('id');
    items.defineDone();
    call missing(id);
  end;

  set have;
   
  if items.check(key:id_a) ne 0 and items.check(key:id_b) ne 0 then do;
    output;
    items.add(key:id_a, data:id_a);
    items.add(key:id_b, data:id_b);
  end;

  drop id;
run;
user_12499620
Related