Patrick By the way, I did some research and I found a way to delete an existing hash table before loading in new information to the same-named hash. For each new member of a BY group, the hash can be deleted in the following manner: if first.JournalTitle and _n_ ne 1 then do; h.delete(); h.=_new_ hash; ... end; Basically, for a hash object h defined in a previous section in the same DATA step, a new hash table can be created per each new value of JournalTitle, which removes the possibility of any memory issues arising from utilizing the same hash reference without doing the clearing out of the prior information. Still working on loading the new data so I won't be able to revisit the test code until later. ... View more

Thanks for offering your help! It definitely is a big challenge... Definitely I will be happy to share some data if you wanted to play around with it - I'm actually awaiting a refreshed version of the dataset with more variables, so the code might change slightly to incorporate new labeling etc. Probably won't happen until next week though so I figure I can cut a sample then and get the most recent. In the meantime, I tested the code Patrick sent on a larger amount of observations. Based on some log feedback, I'm getting 11 min. to process 5k observations, and if I remember correctly roughly 2 hrs to process 50k observations. I think the time spent is non-linear and will be variant depending on the amount of observations contained per journal - as I understand it the hash object has to essentially repopulate itself for each journal, larger journals will take longer of course as every row is being compared to all other rows in the same journal without replacement. I think the possible duplicate dataset will be a bit bulky given that we will have 100m rows - I have to think about this further but its definitely a start. I'm possibly considering making the cleanup two part - examine the SPEDIS value on the article title alone, then examine the SPEDIS of the other match variables. If both are <10 then I'd suspect a true dup and it would alleviate the problem of falsely id'ing a similarly named title in the same journal like for example: Article, vol, issue: X - Part I 142 53 X - Part II 143 35 In this case SPEDIS would be low for the article and high for the concatenation of vol and issue so the latter prevents false positives. Yellow Park 199 12 Yellow Par. 199 12 In this case SPEDIS is low for both so its probably the same article. Just brainstorming - I have to put more thought into it... Anyway I'm going to revisit the problem next week - I'm out Friday unfortunately. Thanks much... 🙂 Hans Message was edited by: Hans ... View more

Hi Patrick, Just wanted to thank you again for that code - it works on my test sample! 🙂 Took me awhile to understand what's going on in there but I think I kind of get it now. I made a few mods, I made the possible duplicates table to be scores between .00001 and 20 as opposed to 0 and 20 since a 0 would basically be the exact title match later on in SPEDIS. As we are assuming if they match exactly on Volume, Issue, PubYear, etc. they are one and the same, so I give them a score of .00001 if everything else is the same but the ArticleTitle. I only want to examine the ones that have a difference in spelling variation in Article, so I added a piece of code before that part to assign a 0 score if ArticleTitle = _ArticleTitle. The SPEDIS takes care of the remainder. An example of the nice result of your code is the following duplication detection, note the minor variations in article spelling, but the very close match on the other variables, the combination of such in the concatentation within your SPEDIS function resulted in a score of 4. Here's a very small partial print of what I am seeing: In order: ArticleTitle, PubYear, Volume, Issue HOW "DEUTSCH" A REQUIEM? ABSOLUTE MUSIC, UNIVERSALITY, AND THE RECEPTION OF BRAHMS'S "EIN DEUTSCHES REQUIEM," OP. 45 1998 1 3 HOW DEUTSCH A REQUIEM? ABSOLUTE MUSIC, UNIVERSALITY, AND THE RECEPTION OF BRAHMS'S EIN DEUTSCHES REQUIEM, OP. 45 1998 1 3-19 There are some instances of false positives due to the way the article title comes in: FACULTY POSITIONS AS A CAREER CHOICE FOR PROFESSIONALS--PART II 1991 4 329 FACULTY POSITIONS AS A CAREER CHOICE FOR PROFESSIONALS--PART II 1991 4 329 FACULTY POSITIONS AS A CAREER CHOICE FOR PROFESSIONALS--PART I 1991 3 202 FACULTY POSITIONS AS A CAREER CHOICE FOR PROFESSIONALS--PART I 1991 3 202 I'm thinking of somehow maybe creating a last word variable for the article to get around this situation and perhaps overpenalize non-matches to alleviate the issue. Still have a long way to go but thank you for getting me started! :) Message was edited by: Hans ... View more

Patrick - Now that I've read up on hashing I can now appreciate how much time (both processing and coding) your method will save. Our SAS is housed on a UNIX box so yes I think it should be able to handle it, I often batch large macro analytical coding overnight. In regards to the hash declaration, I'm going to do some research - will post here I find anything on the same. I've actually created a test dataset comprising of only 4 million rows for experimentation, I'll see how the coding plays out against that set in the next few days. I appreciate your coding thoughts - this is something I haven't used before and I wouldn't have thought of it. 🙂 Hans ... View more

Art - Thank you for the code. The journals actually are already cleaned through another script, however you gave me an idea, I'm thinking maybe to ease up on the processing time, the dataset could be broken into thousands of smaller datasets based on journal title which may make the cleanup easier as the smaller datasets won't have to account for different journals in the same dataset. However this may also complicate the process in other ways by requiring a macro code to run against all such generated datasets... I'll have to think about it but thanks for your input! Ksharp - Thanks for the SPEDIS coding - it makes perfect sense and I think 10 is a good acceptable distance. Unfortunately I ran the SPEDIS idea across to the team and they want a multi-variable scoring match as they think it will reduce any potential false positives vs. using spelling distance. Patrick - That code is great, I haven't touched hash tables for awhile and will need to review, but if I do end up preserving the main dataset rather than breaking it up into smaller ones, I think I will be giving this a try. The scoring threshold is still being thought out conceptually by my team but I think we are in agreement that it will be a combination of Start Page, PubYear, potentially the first and last words of the article title itself, and author. We will also include volume and issue in the match and would assign a heavy weight to this relative to the others. (a match on volume and issue means it's basically in the same "book" so to speak, a journal being composed of volumes containing issues which in turn contain 5 to 10 articles each issue). Again, thinking through it a little more - I'm thinking of breaking up the data into smaller datasets but am concerned it might complicate things as it would remove the problem of memory / CPU issues but would result in thousands of different datasets that would require a macro to be written to process each dataset. Any further ideas will be most appreciated, thanks so much for these code examples, I'm slightly less lost now. 🙂 Hans ... View more

Art / Ksharp, thanks for your help. Art - The data has already been trimmed and upper cased, I also COMPRESS out any dashes. An example of some observations would be (sorry if text wraps): Obs JournalTitle ArticleTitle Volume Issue PubYear StartPage FirstWordArticle FirstWordAuthor 1 LANCET BETA CAROTENE DEFICIENCY 324 1 2005 131 BETA SILMAN 2 LANCET B CAROTENE DEF 324 1 2005 131 B SILMAN 3 LANCET THROMBOSIS AND NEUROPATHY 2006 THROMBOSIS 4 SCIENCE IMMUNOGLOBULIN ALLOTYPES 11 4 2005 IMMUNOGLOBULIN RHYS Note that obs 1 & 2 are basically really the same article which is the point of the clean up to attempt to correct irregular article spellings. The problem is that I need to do the cleanup on a dataset that has ~100 mil observations across around 200k distinct journals. Article cleanups should be specific to each journal so that I don't accidentally clean an article from another journal. Ksharp - Yes I was thinking of doing a SPEDIS function as an alternative to scoring, however to my understanding the function requires an entry to look for within the function i.e. Value = spedis(ArticleTitle, 'IMMUNOGLOBULIN ALLOTYPES'); I would need to restrict it so that it only runs within the same journal, and also I would need some way of automatically feeding in the different article values that are present within each journal against all the other records in the same journal to do the fuzzy match, and don't really know how to conceptualize how to go about that. Any advice / thoughts are appreciated. Thanks for your help... Message was edited by: Hans ... View more