Hi, below is a combination of ideas. I am storing a FREQxAMOUNT matrix in a hash object, and updating it if new information is available. (Note: this technique works only, if your LO_FREQ and LO_AMOUNT values are always the same for every date, or if this info is missing for a specific date. In your input data you shoul alway have an INTRATE for a transaction on the same day or before! If this is not true, some additional coding is needed.) I think it would also work without a hash table, but I wanted to keep the idea of the hash table, since in that case the transactions should be sorted only by TABLE and EFFECTIVEDATE (not by LO_FREQ and LO_AMOUNT). Even the TABLE variable could be included in the hash - this would enable pure "stream processing" (no need for sortinig), provided your data originally is sorted by date. (Often transactional data comes already sorted.) proc sort data=MATRIX; by MATRIXTABLE LO_DATE; run; DATA LOOKUP(); IF _N_ = 1 THEN DO; DECLARE HASH H(ORDERED:"D",HASHEXP:16); H.DEFINEKEY("LO_FREQ","LO_AMOUNT"); H.DEFINEDATA("LO_FREQ","LO_AMOUNT","INTRATE"); H.DEFINEDONE(); DECLARE HITER HI("H"); END; SET MATRIX(in=inM rename=(MATRIXTABLE=TABLE LO_DATE=EFFECTIVEDATE)) HISTORYTRANS(); by TABLE EFFECTIVEDATE /*FREQ AMOUNT*/; if inM then do;/*record from MATRIX*/ rc=h.add(); if rc then h.replace(); end; else do;/*record from HISTORYTRANS*/ RC = HI.FIRST(); DO WHILE (RC = 0); IF LO_FREQ <= FREQ and LO_AMOUNT <= AMOUNT THEN DO; output; LEAVE; END; RC = HI.NEXT(); END; end; RUN;
... View more
