@jarg: The hash object can make many things more elegant, simpler, and faster. That's why @DonH and I have written a whole book about it:   https://www.sas.com/store/books/categories/examples/data-management-solutions-using-sas-hash-table-operations-a-business-intelligence-case-study/prodBK_69153_en.html    Though in this case, the concept of the program is quite simple: store VAR_REF from REF in a lookup table for each record from BIG: search VAR in the table, and if found, output as "equal" then search each word in VAR in the table, and if found, output it and the corresponding VAR_REF The concept would remain the same if a different type of lookup table were used. However, the hash table makes the process of organizing the lookup table and searching it a breeze - and searches in O(1) time to boot. I agree that it behooves any SAS programmer to make the hash object, to a lesser or larger degree, an instrument in one's toolbox. After all, it's been an integral part of Base SAS for 16 years and counting!     Kind regards Paul D.   ... View more

@AJ_Brien: A non-SQL way you can approach this with depends on whether your input is sorted by ACC or not. If it is sorted: data have ; input form id dt :datetime18. acc $ flag $ ; format dt datetime. ; cards ; 60663233 2210 01Oct2019:00:00:00 test Y 60699093 2210 03Oct2019:00:00:00 test . 60699143 2210 03Oct2019:00:00:00 test . 60699213 2210 03Oct2019:00:00:00 test Y 60699983 2210 03Oct2019:00:00:00 test . 60699998 2210 03Oct2019:00:00:00 test . 60700023 2210 03Oct2019:00:00:00 test . 61120139 2317 12Nov2019:00:00:00 test1 Y ; run ; data want ; do until (last.acc) ; set have (where=(flag="Y")) curobs = _q ; by acc ; if dt < _n_ then continue ; _n_ = dt ; _s = _q ; end ; set have point = _s ; run ; If it is not sorted: data want ; dcl hash h () ; h.definekey ("acc") ; h.definedata ("_n_", "_s") ; h.definedone () ; do until (z) ; set have curobs = _q end = z ; where flag = "Y" ; if h.find() ne 0 then call missing (_n_, _s) ; if dt <= _n_ then continue ; _n_ = dt ; _s = _q ; h.replace() ; end ; dcl hiter hi ("h") ; do while (hi.next() = 0) ; set have point = _s ; output ; end ; run ; In both  cases, if there's a tie between two "Y" records with the same maximal date, the first occurrence is selected. Note that self-merging SQL offered by @novinosrin won't handle this and instead return all the tied records.   Kind regards PaulD. ... View more

@LineMoon: A modicum of hash object usage should do it: data big ; input @1 var $16. ; cards ; first one test number one ; run ; data ref ; input @1 var_ref $16. ; cards ; one first one test number ; run ; data want ; if _n_ = 1 then do ; if 0 then set big ref ; dcl hash h (dataset:"ref") ; h.definekey ("var_ref") ; h.definedone () ; end ; set big ; common_values = var ; if h.find (key:var) = 0 then do ; common_values = "equal" ; output ; end ; do _n_ = 1 to countw (var) ; common_values = scan (var, _n_) ; if h.find (key:common_values) = 0 then output ; end ; run ; Kind regards Paul D.   ... View more

@mkeintz: I wonder if LOSTLINE or LOSTDATALINE would be more, uh, descriptive than LOSTCARD or whether it was debated at some point. If so, I guess the decision was "nope".  ... View more

@Quentin: Quentin, like you, I've learned from Ian that coding something that serves no function should be avoided for two reasons: (1) it may lead to an error and (2) to a confusion. The semicolon after a macro is one of those, and it falls in both categories. A period after a macro reference "just in case" is another, though it falls just into category #2.   Kind regards Paul D. ... View more

@tonybesas: Well, you must've realized that 7 means the number of variables in question you showed in your input sample. Obviously, now you have more. So, just to account for (almost) all eventualities, instead of: array x [2,7] _temporary_ ; code: array x [2,999999] _temporary_ ;  I doubt you can have more than 999999 variables in ONE, so it should cover the territory. All you risk to lose is extra 16 MB of memory. It would matter 25 years ago on the mainframe with REGION=256K. Nowadays, it's peanuts.    Or just use the hash variant, as it's self-adjusting to the number of variables in ONE and records in TWO, plus it doesn't care whether the orders of the variable names in ONE and values of WORD in TWO match.   Kind regards Paul D. ... View more

@mkeintz: Mark, ha-ha. It's a good thing you'd forgotten about LINES since this is a poison pill - if only the color thing discovered by @novinosrin were the problem. Try this: data have ; infile lines ; input x ; lines ; 1 2 run ; What do you get? Right, this: ERROR: No logical assign for filename LINES. Huh? But now, try this: data have ; infile cards ; * place 1 ; input x ; lines ; * place 2 ; 1 2 run ;  And everything is hunky-dory. Bottom line (npi), LINES is never good in place 1, regardless of what is in place 2, even if it's LINES itself. However, CARDS and DATALINES are always good in place 1 irrespective of whether place 2 is occupied by CARDS, LINES, or DATALINES.   I have no first clue why it was necessary to create such a confusion, as if CARDS/CARDS4 weren't good enough. If it was just the desire to cater to a younger audience by shaking off the image of CARDS as something ancient harking back to the mainframe, the introduction of LINES and DATALINES was hardly worth the effort. SAS is still chock full of keywords, and indeed features, borrowed from the mainframe, and I find absolutely nothing wrong with it. Getting rid of them would be tantamount to throwing the baby away with the bathwater. Shall we get rid, just for starters, of: MSGLEVEL LRECL RECFM DISP OLD NEW SHARE MOD  & and && as symbolic references and . and .. as indicators of their termini The concepts of STEP, PROCEDURE, LIBRARY, CATALOG The entire PL/I syntax (including the macro language syntax) upon which the entire SAS language syntax is built ... I guess I'm asking a rhetorical question. I've always coded CARDS, code CARDS, and will always code CARDS - and nothing else. If it should offend some folks inheriting my code who have never seen a punch card, none of my business - they can replace it with DATALINES if they think it adds any value to their code. As far as LINES is concerned, see above.   Kind regards Paul D.   ... View more

@Tom: A keen observation ... that's what I call think.  ... View more

@RTN: This is a variation on the double DoW-loop scheme offered by @PeterClemmensen. The only essential difference is that the first DoW-loop reads only the records needed to determine the minimal difference in case WHERE score_year < start.  data have ; input id start score_year score ; cards ; 1 2015 2003 2 1 2015 2007 0 2 2012 2004 2 2 2012 2005 0 2 2012 2013 1 3 2000 1997 0 3 2000 1999 6 3 2000 2002 4 3 2000 2005 6 4 2000 1998 0 4 2000 2008 1 ; run ; data want (drop = _:) ; do until (last.id) ; set have (where = (score_year < start)) ; by id ; _m = min (_m, start - score_year) ; end ; do until (last.id) ; set have ; by id ; if start <= score_year or start - score_year = _m then output ; end ; run ; An interesting aspect for those who like think in DoW terms is that though the two loops read different number of records from their respective independent input streams, they remain in sync thanks to the second BY.   Kind regards Paul D.      ... View more

@tonybesas: Something like below should work - under the assumption that the order of WORD values in TWO matches that of the like-named variables in ONE: data one ; input file $ baby back ball bear beat begin call ; lines ; text1 3 4 0 1 0 1 0 text2 2 0 0 3 3 1 2 text3 0 0 0 1 0 5 1 text4 1 1 2 1 1 4 1 ; run ; data two ; input word $ pole $ ; lines ; baby f3neg back f1pos ball f1pos bear f2pos beat f2neg begin f2neg call f3pos ; run ; data want ; array x [2,7] _temporary_ ; if _n_ = 1 then do _i_ = 1 by 1 until (z) ; set two (keep = pole) end = z ; x[1,_i_] = input (char (pole, 2), 1.) ; x[2,_i_] = choosen (findc ("np", char (pole,3)), -1, 1) ; end ; set one ; array v baby--call ; array F [3] ; do over v ; _n_ = x[1,_i_] ; f[_n_] = sum (f[_n_], v * x[2,_i_]) ; end ; run ; Or, alternatively, the array X can be replaced by a hash table, which has the advantage of not making the assumption mentioned above, as the attributes for each variable (which F and which sign) are searched by the variable name in the table X: data want (drop = _:) ; if _n_ = 1 then do ; dcl hash x () ; x.definekey ("_w") ; x.definedata ("_n_", "_iorc_") ; x.definedone () ; do _n_ = 1 by 1 until (z) ; set two (rename=word=_w) end = z ; x.add (key:_w, data:input(char(pole,2),1.), data:choosen(findc("np",char (pole,3)),-1,1)) ; end ; end ; set one ; array v baby--call ; array F [3] ; do over v ; x.find (key:vname(v)) ; f[_n_] = sum (f[_n_], v * _iorc_) ; end ; run ; Kind regards Paul D.  ... View more