I am very close to a final solution:. I think I can take care of it with a lot of labels: proc sql;   create table scoresL as   select CLAIMNO, COMMENTTEXT, Text4Matching, NumberForLabels     from     SRS_Comments500 inner join     TextFromExcel on indexw(COMMENTTEXT, Text4Matching)>0   order by CLAIMNO, COMMENTTEXT, Text4Matching; quit; proc print data=scoresL noobs; run; proc transpose data=scoresL out=FlaggedCommentsT(drop=_:) prefix=flag_;   by CLAIMNO;   id NumberForLabels; run; proc print data=FlaggedCommentsT noobs; run; I just need to create a routine to label each of the numbered fields. But, I am getting lots of errors involved with this: The ID value, "flag_6" occurs twice in the same BY group. I cannot figure what is happening here. Any suggestions? ... View more

Thank you again PGStats. Once again I have been humbled by what I am trying to accomplish. I have one more idea brewing here. But, a little background. I have over 1,000,000 million comments used by our insurance company. The goal with part of our predictive modeling is to see if any key words or phrases are predictive of future reserve changes and other indices. We began the process by doing a Text Analysis within Enerprise Miner. From what I have seen this is a very useful tool in itself by creating clusters and/or factors that academically would be very useful. I am trying to take the words and phrases that come out as statisticially significant- and provide further analyses to see if they are statistically meaningful. My goal was to collect hundreds - even thousands of these key words and phrases - then let the bivariate indices such as correlations be our "stepping stone" for figuring if they would be worthy of inclusion in a decision tree model. Ideally I would like to find anywhere from a few dozen to over one hundred words & phrases that could potentially predict reserve changes. Of course these would not be the main predictors. Any suggestions are welcome. Right now I have about a half a dozen different ideas brewing on how I wish to proceed. But no worries about the 1 million records. I have already pulled a random sample of 5,000 for working purposes. ... View more

Wow thanks. I think I am picking up that the Cartesian product is a beast in here. Question - everything seems to be working, but I get many errors like the following: The ID value "'flag_day qrc meet surgery ime ce'n" occurs twice in the same BY group I am assuming that is because my character lengths are too long. But it is sort of necessary for what I am doing. Will there be a limit as to the number of characters I may use, or may I extend the default in SQL? Thanks. ... View more

Thank you very much. It worked, and it worked well. Now let me see if I can throw a curve ball into the mix. The code you provided does an excellent job of summating the scores associated with each of the words. Now, let us think about saying that the scores no longer matter. Now when it ultimately creates a table I would like to create columns for each word or phrase that it finds: Phrase Flag_able_stuff Flag_best Flag_courage phrase one able phrase two able stuff 1 phrase three best 1 phrase four courage 1 phrase five able courage 1 phrase six able stuff courage 1 1 The newly created column for Flag_able_stuff had to put a _ in between the words to work. Can a variation of your excellent SQL code do this? Or any other suggestions? ... View more

I apologize if this is a very old thread. I really like the sql code to accomplish the word searches here. But now I would like to do a search on a phrase instead of just words. But, as you can see I am even having problems with that as my second phrase is not reading in the data correctly. Please let me know of any suggestions. Thank you very much in advance. data phrases; length phrase $200; /* longer, if needed */ input; phrase = trim(lowcase(_infile_)); datalines; phrase one able should be 0 phrase two able stuff should be 3 phrase three best should be 4 phrase four courage should be 5 phrase five able courage should be 5 phrase six able stuff courage should be 8 ; data words; length word $16; input word score; word = lowcase(word); datalines; able stuff 3 best 4 courage 5 ; proc sql; create table scores as select phrase, sum((indexw(phrase, word)>0)*score) as phraseScore from phrases, words group by phrase; proc print; run; ... View more

Thank you for all of the suggestions! ... View more

Thank you so much!!! I believe it is the former SPSS-er in me that realy wanted proc freq to work. I will try all of those suggestions. ... View more

Thank you very much. I did a little investigating using the ODS tagsets.excelxp recommendation you provided. I am thinking it probably would be easiest to simply create the output in such a way so that it is .csv. Is there an option to select in EG that would enable this? Or do I need to go through the processes of downloading the proper program/functionality and write the extra code? Trying to keep as simple as possible. Thank you. ... View more

Thank you very much. I got it to converge after a few hours last night. Thank you for everything. ... View more