topic Re: Complicated matching problem in SAS Programming

Complicated matching problem

PaigeMiller — Mon, 16 Nov 2020 13:28:23 GMT

I have a very large data set, where matching has to be performed according to certain rules. The data set is millions of records, so I'm thinking a hash objects solution might work here, but I don't know how to do that.

I have daily transactions with trans=Type1 or Type2, each with an amount (variable name: TranValueAdj) and date associated with it. I want to match the Type1 record to the first Type2 that has the same amount and ID, such that the date of the Type1 transaction is less than the date of the Type2 transaction. Once a Type1 transaction is matched with a Type2 transaction, neither that Type1 or Type2 transaction can be matched again. When there are multiple transactions with the same ID and amount, this becomes tricky. The variable SourceType is not used in the matching, but needs to be in the output data set.

Below is an example of the actual data, and following that is the desired outcome.

Thanks!

data have;
  infile datalines dsd truncover;
  input id:$3. dateTran:DATE9. Trans:$5. SourceType:$2. TranValueADj:32.;
  format dateTran DATE9.;
datalines4;
001,03JAN2020,Type1,M1,1776
001,03JAN2020,Type1,A2,1824.12
001,03JAN2020,Type1,M1,1000
001,17JAN2020,Type1,A2,1830.88
001,27JAN2020,Type1,M1,375.27
001,27JAN2020,Type1,M1,470.04
001,27JAN2020,Type1,M1,4712.3
001,31JAN2020,Type1,A2,1869.88
002,02JAN2020,Type1,B3,1848
002,03JAN2020,Type1,A2,3351
002,03JAN2020,Type1,A2,871
002,03JAN2020,Type1,A2,5135
002,03JAN2020,Type2,,1000
002,03JAN2020,Type1,A2,1070
002,03JAN2020,Type1,A2,8357
002,06JAN2020,Type1,B3,49425
002,06JAN2020,Type1,B3,61744.1
002,06JAN2020,Type1,B3,11273
002,07JAN2020,Type2,,2198
002,07JAN2020,Type1,B3,36006.95
002,08JAN2020,Type1,B3,29329.5
002,10JAN2020,Type1,B3,695
002,10JAN2020,Type2,,703
002,10JAN2020,Type1,B3,29912.3
002,13JAN2020,Type2,,250
002,13JAN2020,Type1,B3,24951
002,14JAN2020,Type2,,250
002,16JAN2020,Type1,B3,9759.5
002,17JAN2020,Type1,A2,652
002,17JAN2020,Type1,B3,4538
002,21JAN2020,Type1,B3,845
002,21JAN2020,Type1,B3,83436.27
002,21JAN2020,Type1,B3,75
002,21JAN2020,Type1,B3,8814
002,22JAN2020,Type2,,250
002,24JAN2020,Type1,B3,2270
002,24JAN2020,Type1,B3,2258
002,28JAN2020,Type1,B3,9135
003,03JAN2020,Type1,B3,100000
003,03JAN2020,Type1,A2,2061
003,07JAN2020,Type2,,100000
003,13JAN2020,Type1,A2,775.1
003,14JAN2020,Type1,B3,100000
003,16JAN2020,Type2,,100000
003,23JAN2020,Type1,B3,25000
003,24JAN2020,Type1,A2,121.16
003,27JAN2020,Type1,A2,146.67
003,31JAN2020,Type1,A2,1246.7
004,06JAN2020,Type1,A1,300
004,09JAN2020,Type2,,300
004,15JAN2020,Type1,A2,1204.5
004,31JAN2020,Type1,B1,0
004,31JAN2020,Type1,A2,3079.09
005,13JAN2020,Type1,B3,1000
005,21JAN2020,Type1,B3,1000
005,23JAN2020,Type2,,1000
005,28JAN2020,Type1,B3,1000
005,30JAN2020,Type2,,1000
006,02JAN2020,Type1,M1,250
006,03JAN2020,Type1,A2,23.72
006,03JAN2020,Type2,,200
006,06JAN2020,Type1,M1,400
006,09JAN2020,Type2,,400
006,16JAN2020,Type1,M1,150
006,21JAN2020,Type1,M1,190
006,22JAN2020,Type1,M1,200
006,23JAN2020,Type1,M1,200
006,24JAN2020,Type1,M1,184.75
006,27JAN2020,Type1,M1,120
006,28JAN2020,Type2,,200
006,29JAN2020,Type1,M1,550
007,02JAN2020,Type1,B1,0
007,13JAN2020,Type1,MF,180
007,13JAN2020,Type1,M1,175
007,15JAN2020,Type1,M1,240
007,16JAN2020,Type1,M1,440
007,16JAN2020,Type2,,175
007,17JAN2020,Type1,M1,345
007,21JAN2020,Type2,,240
007,22JAN2020,Type2,,440
007,23JAN2020,Type2,,345
;;;;

data want;
  infile datalines dsd truncover;
  input id:$4. dateTran:DATE9. Trans:$5. SourceType:$2. TranValueADj:32. MatchedDate:DATE9. MatchedAmt:$6.;
  format dateTran DATE9. MatchedDate DATE9.;
  label id="id" dateTran="dateTran" Trans="Trans" SourceType="SourceType" TranValueADj="TranValueADj" MatchedDate="MatchedDate" MatchedAmt="MatchedAmt";
datalines4;
A003,03JAN2020,Type1,B3,100000,07JAN2020,100000
A003,03JAN2020,Type1,A2,2061,,.
A003,13JAN2020,Type1,A2,775.1,,.
A003,14JAN2020,Type1,B3,100000,16JAN2020,100000
A003,23JAN2020,Type1,B3,25000,,.
A003,24JAN2020,Type1,A2,121.16,,.
A003,27JAN2020,Type1,A2,146.67,,.
A003,31JAN2020,Type1,A2,1246.7,,.
A001,03JAN2020,Type1,M1,1776,,.
A001,03JAN2020,Type1,A2,1824.12,,.
A001,03JAN2020,Type1,M1,1000,,.
A001,17JAN2020,Type1,A2,1830.88,,.
A001,27JAN2020,Type1,M1,375.27,,.
A001,27JAN2020,Type1,M1,470.04,,.
A001,27JAN2020,Type1,M1,4712.3,,.
A001,31JAN2020,Type1,A2,1869.88,,.
A002,02JAN2020,Type1,B3,1848,,.
A002,03JAN2020,Type1,A2,3351,,.
A002,03JAN2020,Type1,A2,871,,.
A002,03JAN2020,Type1,A2,5135,,.
A002,03JAN2020,Type2,,1000,,.
A002,03JAN2020,Type1,A2,1070,,.
A002,03JAN2020,Type1,A2,8357,,.
A002,06JAN2020,Type1,B3,49425,,.
A002,06JAN2020,Type1,B3,61744.1,,.
A002,06JAN2020,Type1,B3,11273,,.
A002,07JAN2020,Type2,,2198,,.
A002,07JAN2020,Type1,B3,36006.95,,.
A002,08JAN2020,Type1,B3,29329.5,,.
A002,10JAN2020,Type1,B3,695,,.
A002,10JAN2020,Type2,,703,,.
A002,10JAN2020,Type1,B3,29912.3,,.
A002,13JAN2020,Type2,,250,,.
A002,13JAN2020,Type1,B3,24951,,.
A002,14JAN2020,Type2,,250,,.
A002,16JAN2020,Type1,B3,9759.5,,.
A002,17JAN2020,Type1,A2,652,,.
A002,17JAN2020,Type1,B3,4538,,.
A002,21JAN2020,Type1,B3,845,,.
A002,21JAN2020,Type1,B3,83436.27,,.
A002,21JAN2020,Type1,B3,75,,.
A002,21JAN2020,Type1,B3,8814,,.
A002,22JAN2020,Type2,,250,,.
A002,24JAN2020,Type1,B3,2270,,.
A002,24JAN2020,Type1,B3,2258,,.
A002,28JAN2020,Type1,B3,9135,,.
A004,06JAN2020,Type1,A1,300,09JAN2020,300
A004,15JAN2020,Type1,A2,1204.5,,.
A004,31JAN2020,Type1,B1,0,,.
A004,31JAN2020,Type1,A2,3079.09,,.
A005,13JAN2020,Type1,B3,1000,23JAN2020,1000
A005,21JAN2020,Type1,B3,1000,,.
A005,28JAN2020,Type1,B3,1000,30JAN2020,1000
A006,02JAN2020,Type1,M1,250,,.
A006,03JAN2020,Type1,A2,23.72,,.
A006,03JAN2020,Type2,,200,,.
A006,06JAN2020,Type1,M1,400,09JAN2020,400
A006,16JAN2020,Type1,M1,150,,.
A006,21JAN2020,Type1,M1,190,,.
A006,22JAN2020,Type1,M1,200,28JAN2020,200
A006,23JAN2020,Type1,M1,200,,.
A006,24JAN2020,Type1,M1,184.75,,.
A006,27JAN2020,Type1,M1,120,,.
A006,29JAN2020,Type1,M1,550,,.
A007,02JAN2020,Type1,B1,0,,.
A007,13JAN2020,Type1,MF,180,,.
A007,13JAN2020,Type1,M1,175,16JAN2020,175
A007,15JAN2020,Type1,M1,240,21JAN2020,240
A007,16JAN2020,Type1,M1,440,22JAN2020,440
A007,17JAN2020,Type1,M1,345,23JAN2020,345
;;;;

Re: Complicated matching problem

novinosrin — Mon, 16 Nov 2020 14:02:47 GMT

Good morning Sir @PaigeMiller Thank you for the question. Just a couple of clarifications please, though may sound silly/lame as I am perhaps not reading between the lines-

1. WANT IDs have prefix letters unlike HAVE ID's. Any reason why that we should pay attention to?

2. The order of the WANT dataset by ID is not the same as HAVE. Is there a specific order you are after?

Re: Complicated matching problem

PaigeMiller — Mon, 16 Nov 2020 14:04:13 GMT

@novinosrin wrote:

Good morning Sir @PaigeMiller Thank you for the question. Just a couple of clarifications please, though may sound silly/lame as I am perhaps not reading between the lines-

1. WANT IDs have prefix letters unlike HAVE ID's. Any reason why that we should pay attention to?

2. The order of the WANT dataset by ID is not the same as HAVE. Is there a specific order you are after?

1. Ignore the prefix letters

2. WANT order not important as long as the matching is correct

Re: Complicated matching problem

PeterClemmensen — Mon, 16 Nov 2020 14:17:38 GMT

@PaigeMiller can a type2 transaction occur with the same amount on the same day?

And if such two occur, should only one of them be prohibited from being matched again?

Re: Complicated matching problem

PaigeMiller — Mon, 16 Nov 2020 14:22:02 GMT

@PeterClemmensen wrote:

@PaigeMiller can a type2 transaction occur with the same amount on the same day?

And if such two occur, should only one of them be prohibited from being matched again?

Yes to both.

Re: Complicated matching problem

PeterClemmensen — Tue, 17 Nov 2020 10:08:24 GMT

I think this does the job ?

data have;
  infile datalines dsd truncover;
  input id:$3. dateTran:DATE9. Trans:$5. SourceType:$2. TranValueADj:32.;
  format dateTran DATE9.;
datalines4;
001,03JAN2020,Type1,M1,1776
001,03JAN2020,Type1,A2,1824.12
001,03JAN2020,Type1,M1,1000
001,17JAN2020,Type1,A2,1830.88
001,27JAN2020,Type1,M1,375.27
001,27JAN2020,Type1,M1,470.04
001,27JAN2020,Type1,M1,4712.3
001,31JAN2020,Type1,A2,1869.88
002,02JAN2020,Type1,B3,1848
002,03JAN2020,Type1,A2,3351
002,03JAN2020,Type1,A2,871
002,03JAN2020,Type1,A2,5135
002,03JAN2020,Type2,,1000
002,03JAN2020,Type1,A2,1070
002,03JAN2020,Type1,A2,8357
002,06JAN2020,Type1,B3,49425
002,06JAN2020,Type1,B3,61744.1
002,06JAN2020,Type1,B3,11273
002,07JAN2020,Type2,,2198
002,07JAN2020,Type1,B3,36006.95
002,08JAN2020,Type1,B3,29329.5
002,10JAN2020,Type1,B3,695
002,10JAN2020,Type2,,703
002,10JAN2020,Type1,B3,29912.3
002,13JAN2020,Type2,,250
002,13JAN2020,Type1,B3,24951
002,14JAN2020,Type2,,250
002,16JAN2020,Type1,B3,9759.5
002,17JAN2020,Type1,A2,652
002,17JAN2020,Type1,B3,4538
002,21JAN2020,Type1,B3,845
002,21JAN2020,Type1,B3,83436.27
002,21JAN2020,Type1,B3,75
002,21JAN2020,Type1,B3,8814
002,22JAN2020,Type2,,250
002,24JAN2020,Type1,B3,2270
002,24JAN2020,Type1,B3,2258
002,28JAN2020,Type1,B3,9135
003,03JAN2020,Type1,B3,100000
003,03JAN2020,Type1,A2,2061
003,07JAN2020,Type2,,100000
003,13JAN2020,Type1,A2,775.1
003,14JAN2020,Type1,B3,100000
003,16JAN2020,Type2,,100000
003,23JAN2020,Type1,B3,25000
003,24JAN2020,Type1,A2,121.16
003,27JAN2020,Type1,A2,146.67
003,31JAN2020,Type1,A2,1246.7
004,06JAN2020,Type1,A1,300
004,09JAN2020,Type2,,300
004,15JAN2020,Type1,A2,1204.5
004,31JAN2020,Type1,B1,0
004,31JAN2020,Type1,A2,3079.09
005,13JAN2020,Type1,B3,1000
005,21JAN2020,Type1,B3,1000
005,23JAN2020,Type2,,1000
005,28JAN2020,Type1,B3,1000
005,30JAN2020,Type2,,1000
006,02JAN2020,Type1,M1,250
006,03JAN2020,Type1,A2,23.72
006,03JAN2020,Type2,,200
006,06JAN2020,Type1,M1,400
006,09JAN2020,Type2,,400
006,16JAN2020,Type1,M1,150
006,21JAN2020,Type1,M1,190
006,22JAN2020,Type1,M1,200
006,23JAN2020,Type1,M1,200
006,24JAN2020,Type1,M1,184.75
006,27JAN2020,Type1,M1,120
006,28JAN2020,Type2,,200
006,29JAN2020,Type1,M1,550
007,02JAN2020,Type1,B1,0
007,13JAN2020,Type1,MF,180
007,13JAN2020,Type1,M1,175
007,15JAN2020,Type1,M1,240
007,16JAN2020,Type1,M1,440
007,16JAN2020,Type2,,175
007,17JAN2020,Type1,M1,345
007,21JAN2020,Type2,,240
007,22JAN2020,Type2,,440
007,23JAN2020,Type2,,345
;;;;

data temp / view=temp;
   set have;
   n = _N_;
run;

data want (drop=n);
   if _N_ = 1 then do;
      dcl hash h(dataset : "temp(where = (Trans='Type2') 
                            rename = (dateTran=MatchedDate TranValueADj=MatchedAmt))",
                 multidata : "Y");
      h.definekey("id", "MatchedAmt");
      h.definedata("MatchedDate", "MatchedAmt", "n");
      h.definedone();

      dcl hash u();
      u.definekey("n");
      u.definedone();
   end;

   set temp;
   if u.check() = 0 then delete;

   if Trans = "Type1" then do;
      do while (h.do_over(key : id, key : TranValueADj) = 0);
         if dateTran < MatchedDate then do;
            u.ref();
            h.removedup();
            leave;
         end;
         else call missing(MatchedDate, MatchedAmt, n);
      end;
   end;

   format MatchedDate date9.;
run;

Re: Complicated matching problem

PaigeMiller — Mon, 16 Nov 2020 14:58:25 GMT

Thanks, @PeterClemmensen . Looks good on the sample data. We're going to try it on millions of records, keep your fingers crossed!

Re: Complicated matching problem

novinosrin — Mon, 16 Nov 2020 18:31:31 GMT

data have;
  infile datalines dsd truncover;
  input id:$3. dateTran:DATE9. Trans:$5. SourceType:$2. TranValueADj:32.;
  format dateTran DATE9.;
datalines4;
001,03JAN2020,Type1,M1,1776
001,03JAN2020,Type1,A2,1824.12
001,03JAN2020,Type1,M1,1000
001,17JAN2020,Type1,A2,1830.88
001,27JAN2020,Type1,M1,375.27
001,27JAN2020,Type1,M1,470.04
001,27JAN2020,Type1,M1,4712.3
001,31JAN2020,Type1,A2,1869.88
002,02JAN2020,Type1,B3,1848
002,03JAN2020,Type1,A2,3351
002,03JAN2020,Type1,A2,871
002,03JAN2020,Type1,A2,5135
002,03JAN2020,Type2,,1000
002,03JAN2020,Type1,A2,1070
002,03JAN2020,Type1,A2,8357
002,06JAN2020,Type1,B3,49425
002,06JAN2020,Type1,B3,61744.1
002,06JAN2020,Type1,B3,11273
002,07JAN2020,Type2,,2198
002,07JAN2020,Type1,B3,36006.95
002,08JAN2020,Type1,B3,29329.5
002,10JAN2020,Type1,B3,695
002,10JAN2020,Type2,,703
002,10JAN2020,Type1,B3,29912.3
002,13JAN2020,Type2,,250
002,13JAN2020,Type1,B3,24951
002,14JAN2020,Type2,,250
002,16JAN2020,Type1,B3,9759.5
002,17JAN2020,Type1,A2,652
002,17JAN2020,Type1,B3,4538
002,21JAN2020,Type1,B3,845
002,21JAN2020,Type1,B3,83436.27
002,21JAN2020,Type1,B3,75
002,21JAN2020,Type1,B3,8814
002,22JAN2020,Type2,,250
002,24JAN2020,Type1,B3,2270
002,24JAN2020,Type1,B3,2258
002,28JAN2020,Type1,B3,9135
003,03JAN2020,Type1,B3,100000
003,03JAN2020,Type1,A2,2061
003,07JAN2020,Type2,,100000
003,13JAN2020,Type1,A2,775.1
003,14JAN2020,Type1,B3,100000
003,16JAN2020,Type2,,100000
003,23JAN2020,Type1,B3,25000
003,24JAN2020,Type1,A2,121.16
003,27JAN2020,Type1,A2,146.67
003,31JAN2020,Type1,A2,1246.7
004,06JAN2020,Type1,A1,300
004,09JAN2020,Type2,,300
004,15JAN2020,Type1,A2,1204.5
004,31JAN2020,Type1,B1,0
004,31JAN2020,Type1,A2,3079.09
005,13JAN2020,Type1,B3,1000
005,21JAN2020,Type1,B3,1000
005,23JAN2020,Type2,,1000
005,28JAN2020,Type1,B3,1000
005,30JAN2020,Type2,,1000
006,02JAN2020,Type1,M1,250
006,03JAN2020,Type1,A2,23.72
006,03JAN2020,Type2,,200
006,06JAN2020,Type1,M1,400
006,09JAN2020,Type2,,400
006,16JAN2020,Type1,M1,150
006,21JAN2020,Type1,M1,190
006,22JAN2020,Type1,M1,200
006,23JAN2020,Type1,M1,200
006,24JAN2020,Type1,M1,184.75
006,27JAN2020,Type1,M1,120
006,28JAN2020,Type2,,200
006,29JAN2020,Type1,M1,550
007,02JAN2020,Type1,B1,0
007,13JAN2020,Type1,MF,180
007,13JAN2020,Type1,M1,175
007,15JAN2020,Type1,M1,240
007,16JAN2020,Type1,M1,440
007,16JAN2020,Type2,,175
007,17JAN2020,Type1,M1,345
007,21JAN2020,Type2,,240
007,22JAN2020,Type2,,440
007,23JAN2020,Type2,,345
;;;;


data want;
 if _n_=1 then do;
   dcl hash h(multidata:'y');
   h.definekey('TranValueADj');
   h.definedata('_dateTran');
   h.definedone();
   dcl hash h1(multidata:'y',hashexp:20);
   h1.definekey('_dateTran','Matchedamt');
   h1.definedata('MatchedDate','Matchedamt');
   h1.definedone();
 end;
 array t(-138061:2936547) _temporary_;
 call missing(of t(*));
 do _n_=1 by 1 until(last.id);
  set have;
  by id;
  if trans='Type1' then do;
   _dateTran=dateTran;
   h.add();
  end;
  else if h.find()=0 then do;
   MatchedDate=dateTran;
   MatchedAmt=TranValueADj;
   t(MatchedDate)=MatchedDate;
   h1.add();
   _rc=h.removedup();
  end;
 end;
 do _n_=1 to _n_;
  set have;
  call missing(MatchedDate,MatchedAmt);
  if t(dateTran) and trans='Type2' then continue;
  if h1.num_items then do;
    _iorc_=h1.find(key:datetran,key:TranValueADj);
	output;
  end;
  else output;
 end; 
 h.clear();
 h1.clear();
 format  MatchedDate date9.;
 drop _:;
run;

proc print noobs;run;

Re: Complicated matching problem

novinosrin — Mon, 16 Nov 2020 17:24:58 GMT

Sir @PaigeMiller FYI- I made a couple of edits to the above. Also at any point you want to test the code, please feel free to let me know if you need a details of the code in piecemeal. A nudge online/offline will alert me just in case.

Re: Complicated matching problem

PaigeMiller — Mon, 16 Nov 2020 17:26:02 GMT

Thanks, we'll see how it goes!

Re: Complicated matching problem

PeterClemmensen — Mon, 16 Nov 2020 20:43:30 GMT

Fingers crossed 🙂

Re: Complicated matching problem

PaigeMiller — Mon, 16 Nov 2020 21:16:13 GMT

The @PeterClemmensen solution run of over 50,000,000 records seems correct so far, but more checking is warranted.

The @novinosrin solution is now running.

Re: Complicated matching problem

novinosrin — Tue, 17 Nov 2020 01:29:29 GMT

One more logic clarification in the expected output please -

why 21jan isnt matched with 30jan as it occurs before 28th?

Re: Complicated matching problem

mkeintz — Tue, 17 Nov 2020 04:58:43 GMT

@novinosrin wrote:

One more logic clarification in the expected output please -

why 21jan isnt matched with 30jan as it occurs before 28th?

I had the same question.

Re: Complicated matching problem

mkeintz — Wed, 18 Nov 2020 23:52:52 GMT

For ID A005, you have 5 values of 1,000

Type 1 Jan 13 is matched to Type2 on Jan 23
Type 1 Jan 21 is not matched

Type1 of Jan 28 is matched to Type2 on Jan 30

Why is the Type1 of Jan 21 not matched to the earliest remaining Type2 - i.e. Jan 30? Which would leave the Jan 28 Type 1 unmatched. That's what my program below does.

Should we assume that once a given type 2 is matched, no earlier unmatched type 1 is eligible for some later type 2?

data have ;
  infile datalines dsd truncover;
  length id $4;
  input id:$3. dateTran:DATE9. Trans:$5. SourceType:$2. TranValueADj:32.;
  id=cats('A',id);
  format dateTran DATE9.;
datalines4;
001,03JAN2020,Type1,M1,1776
001,03JAN2020,Type1,A2,1824.12
001,03JAN2020,Type1,M1,1000
001,17JAN2020,Type1,A2,1830.88
001,27JAN2020,Type1,M1,375.27
001,27JAN2020,Type1,M1,470.04
001,27JAN2020,Type1,M1,4712.3
001,31JAN2020,Type1,A2,1869.88
002,02JAN2020,Type1,B3,1848
002,03JAN2020,Type1,A2,3351
002,03JAN2020,Type1,A2,871
002,03JAN2020,Type1,A2,5135
002,03JAN2020,Type2,,1000
002,03JAN2020,Type1,A2,1070
002,03JAN2020,Type1,A2,8357
002,06JAN2020,Type1,B3,49425
002,06JAN2020,Type1,B3,61744.1
002,06JAN2020,Type1,B3,11273
002,07JAN2020,Type2,,2198
002,07JAN2020,Type1,B3,36006.95
002,08JAN2020,Type1,B3,29329.5
002,10JAN2020,Type1,B3,695
002,10JAN2020,Type2,,703
002,10JAN2020,Type1,B3,29912.3
002,13JAN2020,Type2,,250
002,13JAN2020,Type1,B3,24951
002,14JAN2020,Type2,,250
002,16JAN2020,Type1,B3,9759.5
002,17JAN2020,Type1,A2,652
002,17JAN2020,Type1,B3,4538
002,21JAN2020,Type1,B3,845
002,21JAN2020,Type1,B3,83436.27
002,21JAN2020,Type1,B3,75
002,21JAN2020,Type1,B3,8814
002,22JAN2020,Type2,,250
002,24JAN2020,Type1,B3,2270
002,24JAN2020,Type1,B3,2258
002,28JAN2020,Type1,B3,9135
003,03JAN2020,Type1,B3,100000
003,03JAN2020,Type1,A2,2061
003,07JAN2020,Type2,,100000
003,13JAN2020,Type1,A2,775.1
003,14JAN2020,Type1,B3,100000
003,16JAN2020,Type2,,100000
003,23JAN2020,Type1,B3,25000
003,24JAN2020,Type1,A2,121.16
003,27JAN2020,Type1,A2,146.67
003,31JAN2020,Type1,A2,1246.7
004,06JAN2020,Type1,A1,300
004,09JAN2020,Type2,,300
004,15JAN2020,Type1,A2,1204.5
004,31JAN2020,Type1,B1,0
004,31JAN2020,Type1,A2,3079.09
005,13JAN2020,Type1,B3,1000
005,21JAN2020,Type1,B3,1000
005,23JAN2020,Type2,,1000
005,28JAN2020,Type1,B3,1000
005,30JAN2020,Type2,,1000
006,02JAN2020,Type1,M1,250
006,03JAN2020,Type1,A2,23.72
006,03JAN2020,Type2,,200
006,06JAN2020,Type1,M1,400
006,09JAN2020,Type2,,400
006,16JAN2020,Type1,M1,150
006,21JAN2020,Type1,M1,190
006,22JAN2020,Type1,M1,200
006,23JAN2020,Type1,M1,200
006,24JAN2020,Type1,M1,184.75
006,27JAN2020,Type1,M1,120
006,28JAN2020,Type2,,200
006,29JAN2020,Type1,M1,550
007,02JAN2020,Type1,B1,0
007,13JAN2020,Type1,MF,180
007,13JAN2020,Type1,M1,175
007,15JAN2020,Type1,M1,240
007,16JAN2020,Type1,M1,440
007,16JAN2020,Type2,,175
007,17JAN2020,Type1,M1,345
007,21JAN2020,Type2,,240
007,22JAN2020,Type2,,440
007,23JAN2020,Type2,,345
;;;;

data want (drop=_:);
  if 0 then set have; 
  if _n_=1 then do;
    if 0 then set have (rename=(datetran=matcheddate TranValueADJ=matchedamt));
    declare hash t2 (multidata:'Y');
	  t2.definekey('matchedamt');
	  t2.definedata('matchedamt','matcheddate');
	  t2.definedone();
  end;

  do until (last.id);   /* Populate hash object of type 2 trans */
    set have;
	by id;
	if trans='Type2' then t2.add(key:TranValueADJ,data:TranValueADJ,data:datetran);
  end;

  do until (last.id);
    set have;
	by id;
	call missing(of matched:, _rc);
    if trans='Type1' then do;                        /* Look for a matching  Type2 */
      _rc=t2.find(key:TranValueADJ);
      if _rc=0 then t2.removedup(key:TranValueADJ);  /*If found, remove from hash*/
      output;
	end;
    else if trans='Type2' then do;                   /*See if this type2 not removed yet */
      _rc=t2.find(key:TranValueADJ);
      if _rc=0 and matcheddate=datetran then do;     /*If found, output and remove from hash*/
		t2.removedup();
        call missing(of matched:);
        output;
	  end;
	end;
    else output;     /* Newly added statement, to accommodate non type1/type2 obs*/
  end;

run;

For each id, this program populates a hash object of the type2 transactions. That's the first do until (last.id). In the second do until, each incoming type1 is checked against for a qualifying match in the hash object. If a match is found, that transaction is removed from the hash object. For each incoming type2, if it is not exactly matched in the hash object, if must have already matched a preceding type1, and therefore won't be output. But if it is still there, it is output, and it is also removed from the hash. At the end of each id, the hash object will therefore be empty.

Just read @PaigeMiller 's comment about non type1/type2 records not being transcribed to WANT. I've added an "else output;" statement, which I think should address that oversight.

Another note: When the hash object T2 is indexed by the MatchedAmt and there are ties (allowed by the MULTIDATA:Y argument), the tied dataitems will be stored in the order they are added to the hash object. Since the data are sorted by date, that means the tied values are stored in chronological order. In turn, since the FIND method behavior is to retrieve the first available dataitem for a given key value, it will always retrieve the earliest date for that value (see Page 65 of Dorfman and Henderson's "Data Management Solutions Using SAS Hash Table Operations: A Business Case Study")

Re: Complicated matching problem

PaigeMiller — Tue, 17 Nov 2020 12:55:59 GMT

Hello, everyone, you are correct about the mistake. The JAN30 Type 2 transaction should be match to the JAN21 Type 1 transaction, and there is no match for the JAN28 Type 1 transaction.

Re: Complicated matching problem

Ksharp — Tue, 17 Nov 2020 14:28:08 GMT

Just simple MERGE ?

data have ;
  infile datalines dsd truncover;
  length id $4;
  input id:$3. dateTran:DATE9. Trans:$5. SourceType:$2. TranValueADj:32.;
  id=cats('A',id);
  format dateTran DATE9.;
datalines4;
001,03JAN2020,Type1,M1,1776
001,03JAN2020,Type1,A2,1824.12
001,03JAN2020,Type1,M1,1000
001,17JAN2020,Type1,A2,1830.88
001,27JAN2020,Type1,M1,375.27
001,27JAN2020,Type1,M1,470.04
001,27JAN2020,Type1,M1,4712.3
001,31JAN2020,Type1,A2,1869.88
002,02JAN2020,Type1,B3,1848
002,03JAN2020,Type1,A2,3351
002,03JAN2020,Type1,A2,871
002,03JAN2020,Type1,A2,5135
002,03JAN2020,Type2,,1000
002,03JAN2020,Type1,A2,1070
002,03JAN2020,Type1,A2,8357
002,06JAN2020,Type1,B3,49425
002,06JAN2020,Type1,B3,61744.1
002,06JAN2020,Type1,B3,11273
002,07JAN2020,Type2,,2198
002,07JAN2020,Type1,B3,36006.95
002,08JAN2020,Type1,B3,29329.5
002,10JAN2020,Type1,B3,695
002,10JAN2020,Type2,,703
002,10JAN2020,Type1,B3,29912.3
002,13JAN2020,Type2,,250
002,13JAN2020,Type1,B3,24951
002,14JAN2020,Type2,,250
002,16JAN2020,Type1,B3,9759.5
002,17JAN2020,Type1,A2,652
002,17JAN2020,Type1,B3,4538
002,21JAN2020,Type1,B3,845
002,21JAN2020,Type1,B3,83436.27
002,21JAN2020,Type1,B3,75
002,21JAN2020,Type1,B3,8814
002,22JAN2020,Type2,,250
002,24JAN2020,Type1,B3,2270
002,24JAN2020,Type1,B3,2258
002,28JAN2020,Type1,B3,9135
003,03JAN2020,Type1,B3,100000
003,03JAN2020,Type1,A2,2061
003,07JAN2020,Type2,,100000
003,13JAN2020,Type1,A2,775.1
003,14JAN2020,Type1,B3,100000
003,16JAN2020,Type2,,100000
003,23JAN2020,Type1,B3,25000
003,24JAN2020,Type1,A2,121.16
003,27JAN2020,Type1,A2,146.67
003,31JAN2020,Type1,A2,1246.7
004,06JAN2020,Type1,A1,300
004,09JAN2020,Type2,,300
004,15JAN2020,Type1,A2,1204.5
004,31JAN2020,Type1,B1,0
004,31JAN2020,Type1,A2,3079.09
005,13JAN2020,Type1,B3,1000
005,21JAN2020,Type1,B3,1000
005,23JAN2020,Type2,,1000
005,28JAN2020,Type1,B3,1000
005,30JAN2020,Type2,,1000
006,02JAN2020,Type1,M1,250
006,03JAN2020,Type1,A2,23.72
006,03JAN2020,Type2,,200
006,06JAN2020,Type1,M1,400
006,09JAN2020,Type2,,400
006,16JAN2020,Type1,M1,150
006,21JAN2020,Type1,M1,190
006,22JAN2020,Type1,M1,200
006,23JAN2020,Type1,M1,200
006,24JAN2020,Type1,M1,184.75
006,27JAN2020,Type1,M1,120
006,28JAN2020,Type2,,200
006,29JAN2020,Type1,M1,550
007,02JAN2020,Type1,B1,0
007,13JAN2020,Type1,MF,180
007,13JAN2020,Type1,M1,175
007,15JAN2020,Type1,M1,240
007,16JAN2020,Type1,M1,440
007,16JAN2020,Type2,,175
007,17JAN2020,Type1,M1,345
007,21JAN2020,Type2,,240
007,22JAN2020,Type2,,440
007,23JAN2020,Type2,,345
;;;;
data type1 type2;
 set have;
 if Trans='Type1' then output type1;
  else output type2;
run;
proc sort data=type1;by id TranValueADj;run;
proc sort data=type2;by id TranValueADj;run;
data type2;
 set type2;
 _TranValueADj=TranValueADj;
run;

data want;
 ina=0;inb=0;
 merge type1(in=ina) type2(rename=(dateTran=_dateTran Trans=_Trans SourceType=_SourceType) in=inb);
 by id TranValueADj;
 if not ina then do;dateTran=_dateTran; Trans=_Trans;SourceType=_SourceType;call missing(of _:); end;
 if not inb then  call missing(of _:);
 drop _Trans _SourceType;
run;
proc sort data=want;by id dateTran;run;

Re: Complicated matching problem

PaigeMiller — Tue, 17 Nov 2020 14:35:17 GMT

Thanks, @Ksharp and @mkeintz , we will get around to trying all of the solutions offered as time permits, it does take a long time when the true data set has nearly 60million records and we have other things to do as well.

Re: Complicated matching problem

PaigeMiller — Wed, 18 Nov 2020 18:59:42 GMT

Again, thanks to everyone who contributed. Here are some results

So it seems that the solution 3(@mkeintz) is giving me the correct answer. So each solution runs on the sample data and then I added 6 more accounts to sample data and it seemed to work with additional id’s except the merge one below. However when I run the full October dataset I have the following issues:

@PeterClemmensen solution 1: It’s not dropping enough records in the final solution but I can’t figure out where it’s not dropping them. I think it’s matching correctly and identifies 3162 matched of the 5107 but the final “want” dataset is only reduced by 1604? Not the 3162? Also on this solution, I am not following what he is doing with the U hash object.

Original data rows	want dataset	dataset reduced by
59,650,509	59,648,905	1,604

@novinosrin Solution 2: I am not able to run on the full data set and receive an error “Error: Duplicate Key”

@mkeintz – matches 3177 of the 5107. However it removes and non-type 1 or 2 transactions from final dataset but that is ok cause at least I know what it’s removing vs. solution 1.

@Ksharp Solution 4: the merge data set I did not try on the full data set since the problem below exists

Re: Complicated matching problem

kah1 — Thu, 03 Dec 2020 21:15:17 GMT

To build on the problem. If you only wanted to match the Type 1 and 2 by id if they were <=15 days apart (Type 2 still occurring after 1). How would you modify this program? here are 2 more id's with multiple instances of type 1 with same amount and only 1 type 2. I tried adding another loop with a t2.next() but it wasn't dropping the first find.

have:

id dateTran Trans SourceType TranValueADj
A009 3-Jan-20 Type1 A2 520.84
A009 15-Jan-20 Type1 A2 2008.8
A009 17-Jan-20 Type1 A2 520.84
A009 28-Jan-20 Type1 B2 1041.68
A009 31-Jan-20 Type2 520.84
A009 31-Jan-20 Type1 A2 520.84
A011 2-Jan-20 Type1 A2 1023.3
A011 14-Jan-20 Type1 M1 1023.3
A011 15-Jan-20 Type1 A2 1703.1
A011 17-Jan-20 Type2 1023.3

want -added the number of days based on excel function, i realize sas may be slightly different:

id dateTran Trans SourceType TranValueADj MatchedDate MatchedAmt daysafter
A009 3-Jan-20 Type1 A2 520.84
A009 15-Jan-20 Type1 A2 2008.8
A009 17-Jan-20 Type1 A2 520.84 31-Jan-20 520.84 14.00
A009 28-Jan-20 Type1 B2 1041.68
A011 31-Jan-20 Type1 A2 520.84
A011 2-Jan-20 Type1 A2 1023.3 17-Jan-20 1023.3 15.00
A011 14-Jan-20 Type1 M1 1023.3
A011 15-Jan-20 Type1 A2 1703.1