CODEWITHSUNDEEP
sqlsas
Lorbat
Lorbat

Reputation: 385

Choose first occurrence and then choose the next one down

In SAS(Data Step) or Proc SQL, I want to choose the first occurrence of TransB based on DaysBetweenTrans first and then flag, if TransB has already been chosen then I want the next available one although I also want TransA to be unique as well i.e. TransA is a unique row and TransB is unique too.

For example, the original table looks like this:

TransA TransB DaysBetweenTrans Flag
A 1 1 1
A 2 1 1
B 1 3 1
B 2 2 1
B 3 3 1
C 1 1 1
C 3 4 1

but I want only:

TransA TransB DaysBetweenTrans Flag
A 2 1 1
B 1 3 1
C 3 4 1

I tried using sorting TransA and dedupkey and then sort TranB and dedupkey but no luck. The other way I thought of was to do first.TransA and output. Join back on the original table and remove any TransA and repeat, but there has to be a better way.

Upvotes: 0

Views: 333

Answers (1)

Tom
Tom

Reputation: 51566

You might want to look into SAS procedures for optimization as a straight forward approach of taking the best next match for the current case might not find the best solution.

Here is an approach that uses a HASH to keep track of which targets have already been assigned.

It is not totally clear to me what your preference for ordering are but here is one method. It sounds like you want to find the best match for TRANSB=1. Then for TRANSB=2, etc.

data have;
  input TransA $ TransB $ DaysBetweenTrans Flag;
cards;
A 1 1 0
A 2 1 1
B 1 3 1
B 2 2 1
B 3 3 1
C 1 1 1
C 3 4 1
;

proc sort data=have;
  by transB daysbetweentrans descending flag transA;
run;

data _null_;
  if _n_=1 then do;
    declare hash h(ordered:'Y');
    rc=h.definekey('transA');
    rc=h.definedata('transA','transB','daysbetweentrans','flag');
    rc=h.definedone();
  end;
  set have end=eof;
  by transB;
  if first.transB then found=0;
  retain found;
  if not found then if not h.add() then found=1;
  if eof then do;
    rc=h.output(dataset:'want');
  end;
run;

Results:

                           Days
       Trans    Trans    Between
Obs      A        B       Trans     Flag

 1       A        2         1         1
 2       B        3         3         1
 3       C        1         1         1

Upvotes: 2

Related Questions