Removing pure blank records first is certainly a good idea and would remove some special condition handling.  It adds another data step and some I/O.  If the code can handle it naturally, then it could save the resource.  It's a design decision that we all must make all the time. ... View more

Because your requirement is "any value in any of the column", and the code is using a single hash table, the declaration of the key variable needs to be the longest variable you've got.  I believe there are several comments in the code stating this.  If this was not done, then of course it will not work. ... View more

The _key is uninitialized message is a precaution from the data step compiler.  I think it's saying at the point where it's compiling the hash object, that variable isn't initialized yet, so it's spitting out a note.  It's easily avoided by assigning a value to the variable earlier in the code prior to the declare hash, then it's happy.  With this code, it is initialized later so the code runs fine.  But the message is disturbing to see in the log.  I was just being lazy and only wanted to illustrate the concept, so I didn't clean the code and make it water tight... etc. ... View more

The data set also appears to be sorted by ID and Version, in which case, you can use: proc sort data=have;   *--- only needed if not already sorted by ID and Version;    by id version; run; data want;    set have;    by id;    if last.id;   *--- highest version for each id due to sorting; run; Use which ever method you find more natural and intuitive to you.  Art's methods all work. ... View more

Finally had some free time to work on this a bit.  The attached code uses hash objects to eliminate sorting.  It uses a very simple macro to loop and propagate group changes until no more group id needs to be changed.  This loop until consistent is what makes this capable of navigating the data structure regardless of how deeply nested it needs to go. The limitation of the code is machine limit:  amount of available memory to hold the hash table.  Other than that, it should be able to tackle very large data sets. With this input data: aa bb cc dd ee ff gg hh ii jj kk ll aa ff gg dd mm oo pp pp rr rr ss tt uu vv ww xx yy zz dd xx z1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 1 3 5 7 9 11 13 15 1 5 9 13 1 9 It generates this which is correct by visual inspection:     k1 k2 k3 k4 grp aa bb cc dd 1 ee ff gg hh 1 ii jj kk ll 3 aa ff gg dd 1 mm oo pp 4 pp rr rr ss 4 . tt uu vv ww 6 xx yy zz dd 1 xx z1 1 1 2 7 3 4 7 5 6 7 7 8 7 9 10 7 11 12 7 13 14 7 15 16 7 1 3 7 5 7 7 9 11 7 13 15 7 1 5 7 9 13 7 1 9 7 The OP will need to modify the names and length statements to adapt to the situation, but that should be relatively easy to do. Modifying it to handle different number of key columns is also easy. For true householding where it is "same value within same column" as opposed to this problem of "same value in any of the key columns" is also very easily derived by tweaking this code a tiny bit. ... View more

Why don't you use proc means to first create a dataset with the number of records with non-missing values, # with missing values, and the total, i.e., output N, NMISS and SUM, then you can select only those with N=9 or NMISS=0.  Something like: proc summary data=a missing nway;      class naics;      var employ;      output out=summary(drop=_:)           n=n    nmiss=nmiss    sum=total      ; run; You can then check if nmiss is 0 or not. ... View more

With dynamic data structures, the reference or pointer to it, is very distinct from the memory location or object itself. declare hash H;   is a compile time statement, saying H will contain references to hash objects, but does not create the object. declare hash H Q;   says H and Q are hash references, does not create objects. declare hash H();    says H is a reference to hash objects, and creates an empty hash object. H = _new_ hash();    creates an empty hash object, and stores its address in H.  H now is a reference to the object.  If H previously pointed to another valid hash object, H now no longer points to it, example:      declare hash H;          H is a pointer to hash objects      H = _new_ hash();      create an object.  Store its address in H.      H = _new_ hash();      create another object.  Store its address in H.  The previous object still exist, but H doesn't point to it.  It is orphaned. Multiple reference to same object:      declare hash H Q;     H and Q are pointers to hash objects.      H = _new_ hash();     create an object.  H points to it.      Q = H;                      Q now also point to the same hash object. H.delete();     deletes the object H points to.  But H, the pointer itself, is not deleted.  You can use it to point to another hash object.      declare hash H;      H = _new_ hash();    create object #1      H.delete();               delete object #1      H = _new_ hash();    create object #2      H.delete();                delete object #2 Ahh, the fun and games with object oriented programming.  Never thought it'd show up in a SAS data step. ... View more

And all of this is just a pre-cursor to true text mining where latent semantics analysis and content categorization via singular value decomposition happens... ... View more

I must be missing something here.  (Running SAS 9.1.3 on PC).  The sequence for using hash objects is: declare hash h;          * Tell data step compiler h is a reference to some hash object ; h = _new_ hash();        * Create an instance of hash object, store its pointer in h ; rc = h.defineKey('k');   * Define actual hash object structure ; rc = h.defineData('d');  * Define actual hash object structure ; rc = h.defineDone(); ... rc = h.add(); rc = h.find(); rc = r.remove(); ... rc = h.delete();         * The memory structures of the hash object is released, h now is an invalid reference ; /*   You can then define another hash object and use h to refer to it:  */ h = _new_ hash();        * h now again point to an empty hash object;  rc = h.defineKey('p');   * Define actual hash object structure ; rc = h.defineData('q');  * Define actual hash object structure ; rc = h.defineDone(); ... rc = h.add(); rc = h.find(); rc = r.remove(); ... rc = h.delete();         * Release the hash object, h is no longer a valid hash reference; If you want to have macro independence, you should - use different names for the hash tables, - control when to release each hash object This way the hash objects won't collide with each other. However, if you want to have just one name for all the hash object, meaning you'll never need to use more than one of them at a time, you should - declare the hash reference one time, (as above... only a single declare statement) - use _new_, defineKey(), defineData(), ... to create/instantiate and use the object - when done, use delete() to remove the object from memory - use _new_ again to define another hash object, perhaps with a different structure, but can still use h to point to it - delete() when it's not needed any more Note that if you use h = _new_ hash() without first using h.delete(), the object h was pointing to is now "pointerless" and you will have no way to refer to it, unless you have another reference pointing to it.  The thing to remember is to separate "objects" from "pointers or references"... these are dynamic objects, not static. In the macros above, the hash objects are created and destroyed by the macro.  So when the macro finishes, there are no hash objects left for you to use.  No? ... View more

The issue is that, with any sort of sequential processing like SAS data step, the linkage info can show up much later in the file, which requires (somehow) going back to all previously assigned id, and correct all of them!  From the corrections, more corrections may be necessary, so there is this cascading ripple effect, hence recursion or loop until.  This is the crux of the thorny problem. My code for the labor day challenge was built for householding where the fields are like "name", "address", "phone", "account number".  Any records that share the same value in the same column must be put into a group.  Structurally this is different from the OP where it's the same value in any column, hence my not posting here.  They are closely related though.  WIth some modifications, it should work. Also note that whether it's 2 fields, 4 fields, or n fields, that's not really material, the concept is the same.  And if you think about it, the OP really has only 1 field because it is "same value in any column"!  The records are really statements of arcs or linkages. For my case, wIth the loop until no more inconsistency is found approach on 8 million customer with about 20 million records, it ran for 5-10 hours making around 15+ sweeps through the dataset.  The number of sweeps reflect the order and depth implied by the data.  This problem is interesting in that if you sort the input data stream so as to minimize linkages showing up later, then it runs fast, but if the sorting is opposite, then you experience horrendous run time. Theoretical worst case, we can think of this as a binary tree.  We put the bottom nodes first in the dataset: (1 2) (3 4) (1 3). Now we can replicate this: (1 2) (3 4) (2 3)    (5 6) (7 😎 (5 7)    (1 5)    <this records connects the two subtrees and causes one branch to need to be fully relabeled> and again: (9 10) (11 12) (9 11)     (12 13) (14 15) (12 14)      (9 12)        (1 9) and again, and again, to any depth we wish.  Always the last record binds two large trees, only much bigger and deeper.  And the relabeling can only begin from the top of the tree and need to cascade all the way to the bottom.  If we were using a language that supported a) building linked lists data structure in memory, b) recursive function call, this is easy, provided the machine has sufficient stack depth at run time to accomodate the recursion depth.  But this is not SAS data step at all, hence the pain. We can write code that would generate this binary tree structure to any arbitrary depth  (hmm... should be fun :smileylaugh:)  and feed it to any proposed code to verify correctness. ... View more

Suggest this thread is not necessary since you've got two other discussions on exactly the same thing. ... View more

Before you blow your brains out... just because it works with some test data does not guarantee it will work with arbitrarily deep data.  Worst possible example - everything is connected somehow into one group. ... View more

Gentlemen, just to like add some thoughts on the structure of the problem to this already very long discussion. 1) The unique values within all the fields can be thought of as "nodes" 2) Each record is a statement of linkages or arcs that would connect some nodes together, forming a group 3) Out of each node, there would be multiple linkages (every record that contains that value is linkage statement) This problem is a graph labeling problem: label the nodes so that all nodes that belong to a group share the same label, and all disconnected groups have a different label. The algorithms for the graph labeling problem is not hard to grasp, but is tricky to code in SAS.  The natural language to describe this process requires recursion.  SAS data step has no builtin capability (as far as I know) that supports recusion naturally.  This poses challenges on both the data structure and the algorithm to traverse the nodes and examining / propagating label changes throughout the tree. In my humble opinion, provably correctly code must either show recursion or some sort of loop until no more changes in order to propagate the group id throughout the nodes that all belong in that group at arbitrary depth.  Any code that loops for a finite number of passes without recursion is, by definition, flawed as there would be some data structure that extends beyond the number of passes implied by the code. I have faced similar problems before where we have upwards of 20 million nodes and hundreds of thousands of arcs.  We wrote exceedingly crude SAS data step code to get the job done using the "loop through all nodes and all arcs until no more label changes are required condition is satisfied as we traversed the entire forest".  Running on big UNIX servers, the job took multiple hours to run.  We ended up relying on commercial software to solve the problem as the problem rapidly grew more complex (fuzzy address matching is notoriously difficult, and this was just one of the multiple match field). This is a non-trivial problem when one does not have the right tools that support the right programming paradigm.  Just thought I'd call out the problem and algorithm characteristics as people try to assist the OP.  To reiterate, any code with finite number of passes without recursion, by definition, can only propagate so deep into the forest such that forests deeper than that would not be labeled correctly. ... View more

The approach illustrated above is sequential in nature, but I don't think it covers all potential problems, in particular, what happens when the "linkage info" shows up later in the process?  Example: k1 k2 k3 k4 hid aa bb cc dd  1     all new ee ff gg hh  2     no ovlap with previous ii jj kk ll  3     no overlap yet aa ff cc hh  ??    record 1 & 2 that was distinct now share linkage due to this record When the last record shows up binding record 1,2,4 (they are all connected now), do you want hid for all 3 records to be hid 1 or not?  I would assume you do want them all to be hid 1. This issue of linkage showing up after assignment is typical in finding connected subgraphs (householding, connections,...etc).  This is not solvable in general through sorting, that's why this is always an iterative process until the assignments become stable, i.e., no more group formation is possible. If you can describe what you desire in more detail - specifically covering all possible cases - then perhaps some sharp person familiar with hash objects can point you in the right direction. ... View more