topic Re: Scanning an Observation for a Word within a Variable in SAS Programming

Scanning an Observation for a Word within a Variable

sucksatsas — Fri, 31 May 2013 23:22:15 GMT

Hello- I am trying to count the number of negative and positive words within each "Comment" observation.

Variables: Comment: A list of a website comments

Negative: A list of negative associated words, each observation is one negative word

Positive: A list of positive associated words, each observation is one positive word

I have gathered the information and combined them into one data set, now I am confused how to scan/index each comment observation when I'm looking to see if it contains any word in an entire list and eventually count them to something like this:

Does anyone have any insight?

Comment Negative Positive Rating

"Wow so great" 2

"It's OK" 1

"Good but boring" 0

"Meh" -1

Bad

Meh

boring

Wow

Great

Good

Re: Scanning an Observation for a Word within a Variable

Haikuo — Sat, 01 Jun 2013 00:35:44 GMT

Conveniently this problem becomes another showcase of Hash() power:

data good;

input good$;

cards;

wow

great

good

better

best

;

data bad;

input bad$;

cards;

bad

meh

boring

never

;

data have;

input comment $50.;

cards;

"Wow so great"

"It's OK"

"Good but boring"

"Meh"

"Good Good Good Better Best, Never let it rest"

;

data want;

if _n_=1 then do;

if 0 then set good bad;

declare hash g(dataset:'good', multidata:'y');

g.definekey('good');

g.definedone();

declare hash b(dataset:'bad', multidata:'y');

b.definekey('bad');

b.definedone();

end;

set have;

length s $ 50;

do i=1 by 1 until (missing(s));

s=lowcase(scan(comment,i,'", '));

do rc_good=g.find(key:s) by 0 while (rc_good=0);

gs+1;

rc_good=g.find_next(key:s);

end;

do rc_bad=b.find(key:s) by 0 while (rc_bad=0);

bs+1;

rc_bad=b.find_next(key:s);

end;

Rating=sum(gs,-bs);

call missing(gs, bs);

keep comment rating;

run;

Haikuo

Re: Scanning an Observation for a Word within a Variable

TomKari — Sat, 01 Jun 2013 19:43:19 GMT

Although I agree completely with Haikuo that the hash object, and his program, are the best solution to this problem, it is also logically amenable to SQL:

data good;
input good$;
cards;
wow
great
ok
good
better
best
;

data bad;
input bad$;
cards;
bad
meh
boring
never
;

data have;
input comment $50.;
cards;
"Wow so great"
"It's OK"
"Good but boring"
"Meh"
"Good Good Good Better Best, Never let it rest"
;

data have_words(keep=comment CandidateWord);
set have;
length CandidateWord $ 50;

do i=1 by 1 until (missing(CandidateWord));
CandidateWord=lowcase(scan(comment,i,'", '));

if ~missing(CandidateWord) then
output;
end;
run;

proc sql;
create table want_details as
select comment, 1 as rating
   from have_words h inner join good g on(h.CandidateWord = g.good)
    outer union corresponding
     select comment, -1 as rating
      from have_words h inner join bad b on(h.CandidateWord = b.bad)
;
quit;

proc means noprint nonobs nway;
class comment;
var rating;
output out=want(drop=_type_ _freq_) sum=;
run;

Tom

Re: Scanning an Observation for a Word within a Variable

Linlin — Sun, 02 Jun 2013 01:29:44 GMT

Hi Tom,

You should change "her" in

"Although I agree completely with Haikuo that the hash object, and her program, are the best solution to this problem, it is also logically amenable to SQL:"

to "his". - Linlin

Re: Scanning an Observation for a Word within a Variable

TomKari — Sun, 02 Jun 2013 03:04:09 GMT

OOPS! Thanks, Linlin. It's lost in my excellent but short memory, but somehow I ended up with the impression that our esteemed colleague shares your gender, not mine.

Well, at least I said his program is the best solution...that's gotta get me SOME points!!

Best,

Tom

Re: Scanning an Observation for a Word within a Variable

Haikuo — Sun, 02 Jun 2013 13:59:06 GMT

Thanks, LinLin, for putting the "Y" chromosome back to my online identity. Tom, not being a female really wasn't up to me, LOL. And being a fan since 3 years ago in SAS-L, I am honored for being mentioned by your post,

Haikuo

Re: Scanning an Observation for a Word within a Variable

PGStats — Sun, 02 Jun 2013 17:47:44 GMT

I'd like to see a performance comparison between the methods, including this one using an informat :

data positive;
input word$;
datalines;
wow
great
ok
good
better
best
;

data negative;
input word$;
datalines;
bad
meh
boring
never
;

data have;
length comment $100;
input comment &;
datalines;
Wow so great!
It's OK
Good but boring...
Meh
Good Good Good Better Best, Never let it rest.
;

data emotion;
length type hlo $1 label $4 fmtname $7;
set positive(in=pos) negative(in=neg);
fmtname = "Emotion";
type = "I"; hlo = "U";
start = upcase(word);
label = put(pos - neg, best4.);
run;

proc format cntlin=emotion; run;

data want(drop=pos len i);
set have;
emoScore = 0;
call scan(comment, 1, pos, len);
do i = 2 by 1 while (pos>0);
     emoScore = sum(emoScore, input(substr(comment, pos, len), ?? emotion.));
     call scan(comment, i, pos, len);
     end;
run;

proc print noobs; run;

Re: Scanning an Observation for a Word within a Variable

TomKari — Sun, 02 Jun 2013 23:27:40 GMT

That would be great fun. I always like to make these somewhat real world. @sucksatsas would you be in a position to share your lists of "good" and "bad" words, the number of comments that you want to use this on, and how many words are typically in your comments?

I can generate some data that'll pound all these solutions, and I'll post the results. Hopefully over the next week or two.

Tom

Re: Scanning an Observation for a Word within a Variable

Haikuo — Mon, 03 Jun 2013 00:55:58 GMT

Thanks, Tom, looking forward to your verdict. After spending more time with it, I have made the Hash code more succinct (not sure about the efficient improvement though). At the same time, since Tom is doing the service for us, I 'd like to add Array() approach to the pool of candidates .

data good;

input good$;

cards;

wow

great

good

better

best

;

data bad;

input bad$;

cards;

bad

meh

boring

never

;

data have;

input comment $50.;

cards;

"Wow so great"

"It's OK"

"Good but boring"

"Meh"

"Good Good Good Better Best, Never let it rest"

;

/*Hash()*/

data want_hash;

if _n_=1 then do;

if 0 then set good bad;

declare hash g(dataset:'good', multidata:'y');

g.definekey('good');

g.definedone();

declare hash b(dataset:'bad', multidata:'y');

b.definekey('bad');

b.definedone();

end;

set have;

length s $ 50;

do i=1 by 1 until (missing(s));

s=lowcase(scan(comment,i,'", '));

gs+ (not g.find(key:s));

bs+ (not b.find(key:s));

end;

Rating=sum(gs,-bs);

call missing(gs, bs);

keep comment rating;

run;

/* Array()*/

proc sql noprint;

select nobs into :bad_obs from dictionary.tables where LIBNAME='WORK' AND MEMNAME='BAD';

SELECT NOBS INto :GOOD_OBS FROM DICTIONARY.TABLES WHERE LIBNAME='WORK' AND MEMNAME='GOOD';

QUIT;

data want_array;

if _n_=1 then do;

do i=1 to bobs;

set bad nobs=bobs point=i;

array b(&bad_obs) $10. _temporary_;

b(i)=bad;

end;

do i=1 to gobs;

set good nobs=gobs point=i;

array g(&good_obs) $10. _temporary_;

g(i)=good;

end;

set have;

length s $ 50;

do i=1 by 1 until (missing(s));

s=lowcase(scan(comment,i,'", '));

gs+ (s in g);

bs+ (s in b);

end;

Rating=sum(gs,-bs);

call missing(gs, bs);

keep comment rating;

run;

Haikuo

Re: Scanning an Observation for a Word within a Variable

PGStats — Mon, 03 Jun 2013 02:49:36 GMT

The more the merrier. A proposal with hash and prx :

data positive;
length word $12;
input word;
datalines;
wow
great
ok
good
better
best
;

data negative;
length word $12;
input word;
datalines;
bad
meh
boring
never
;

data have;
length comment $100;
input comment &;
datalines;
Wow so great!
It's OK
Good but boring...
Meh
Good Good Good Better Best, Never let it rest.
;

/* Note: datasets positive and negative could also include a word specific value or

there could be a single table with positive and negative values */

data emotions;
set positive(in=pos) negative(in=neg);
word = upcase(word);
value = pos - neg;
run;

data want;
length word $12;
if prxId = 0 then do;
     declare hash w(dataset:"emotions");
     w.definekey("word");
     w.definedata("value");
     w.definedone();
     call missing(word, value);
     prxId + prxparse("/\w+/");
     end;

set have;
start = 1;
score = 0;
call prxnext(prxId, start, -1, comment, pos, len);
do while (pos > 0);
     word = upcase(substr(comment, pos, len));
     if 0 = w.find() then score + value;
     call prxnext(prxId, start, -1, comment, pos, len);
     end;
drop prxId word value start pos len;
run;

proc print noobs; run;

Re: Scanning an Observation for a Word within a Variable

TomKari — Tue, 16 Jul 2013 14:08:20 GMT

Hello again, after a long absence.

Sorry, everybody, as often happens life interfered with my plans for this. But I think I'm back on track. I have a test environment set up, and have run some preliminary tests. Although SAS is too fast to make the results meaningul, preliminary times are (in order of the programs that were submitted):

Hai.kuo #1

      real time           4.52 seconds
      user cpu time       2.69 seconds
      system cpu time     0.09 seconds

Tom Kari

      real time          83.41 seconds
      user cpu time       6.44 seconds
      system cpu time     7.27 seconds

PGStats #1

      real time           2.06 seconds
      user cpu time       2.01 seconds
      system cpu time     0.04 seconds

Hai.kuo #2

      real time           2.54 seconds
      user cpu time       2.44 seconds
      system cpu time     0.06 seconds

Hai.kuo #3

      real time           6.48 seconds
      user cpu time       6.25 seconds
      system cpu time     0.03 seconds

PGStats #2

      real time           3.06 seconds
      user cpu time       2.93 seconds
      system cpu time     0.07 seconds

I'll be ramping up the data volumes to get some meaningful times, and will report back on both times and memory usage (which should be significant for the array and hash approaches).

Talk to you soon!
Tom

Re: Scanning an Observation for a Word within a Variable

Linlin — Tue, 16 Jul 2013 15:19:17 GMT

PG is the winnor! Congratulations!!!

Re: Scanning an Observation for a Word within a Variable

TomKari — Wed, 17 Jul 2013 16:50:27 GMT

Not so fast!

1. I just finished some minor tweaking to the programs to correct some mistakes I'd made in getting them to all run off of the same data, but I don't think there will be any serious changes, but;

2. We still need to see how they perform at a volume that takes a minute or two.

So far, the only thing we're sure of is that my code is the slowest!:smileyblush:

Tom

Re: Scanning an Observation for a Word within a Variable

Linlin — Wed, 17 Jul 2013 18:25:55 GMT

Tom,

we change the rule to:

The slower the better.

Re: Scanning an Observation for a Word within a Variable

Ksharp — Thu, 18 Jul 2013 01:23:57 GMT

it is very interesting question, Are you doing Context Analysis ?

data good;
  input good $upcase8. ;
  cards;
Wow
Great
OK
Good
  ;
  data bad;
  input bad $upcase8. ;
  cards;
Bad
Meh
boring
  ;
data have;
  input comment $50.;
  cards;
  "Wow so great"     
"It's OK"            
"Good but boring"    
"Meh"
"Good Good Good Better Best, Never let it rest"
;
run;
data want(keep=comment score);
  if _n_=1 then do;
    if 0 then set good ;
    if 0 then set bad;
    declare hash g(dataset:'good');
    g.definekey('good');
    g.definedone();
    declare hash b(dataset:'bad');
      b.definekey('bad');
      b.definedone();
  end;
  set have;
  score=0;
  do i=1 to countw(comment, ,'ka');
   temp=upcase(scan(comment,i, ,'ka')); put temp= ;
   good=temp; if g.find()=0 then score+1;
   bad=temp; if b.find()=0 then score+(-1);
 end;
run;

Ksharp

Re: Scanning an Observation for a Word within a Variable

TomKari — Fri, 19 Jul 2013 21:32:00 GMT

And the results are in!

First series of tests, a large number of comment records with around 300 total good and bad keywords:

Hai.kuo1

      real time           4:20.14
      user cpu time       4:12.83
      system cpu time     3.52 seconds
      memory              11310.40k
      OS Memory           16932.00k

Tom Kari1

As I suspected, SQL wasn't the best approach to this problem. When the decomposition from comments into words plus the SQL join took over an hour (and pretty much froze my machine), I gave up and cancelled it.

PGStats1

      real time           3:22.55
      user cpu time       3:02.28
      system cpu time     2.80 seconds
      memory              11310.40k
      OS Memory           16932.00k

Hai.kuo2

      real time           4:18.11
      user cpu time       4:03.04
      system cpu time     3.15 seconds
      memory              11312.81k
      OS Memory           16932.00k

Hai.kuo3

      real time           10:30.18
      user cpu time       10:21.19
      system cpu time     3.26 seconds
      memory              11312.81k
      OS Memory           16932.00k

PGStats2

      real time           5:41.48
      user cpu time       5:04.57
      system cpu time     6.80 seconds
      memory              11310.40k
      OS Memory           16932.00k

Ksharp1

      real time           5:20.34
      user cpu time       5:14.99
      system cpu time     3.44 seconds
      memory              11312.81k
      OS Memory           17516.00k
      Timestamp           19/07/2013 04:11:51 o'clock PM

Then I massively ramped up the number of keywords, to over 17,000 total good and bad keywords, to see how sensitive the in-memory approaches are to significantly upping the volume.

The results:

Hai.kuo1

      real time           5:34.88
      user cpu time       5:14.20
      system cpu time     3.40 seconds
      memory              11312.81k
      OS Memory           16932.00k

PGStats1

      real time           3:59.54
      user cpu time       3:40.99
      system cpu time     2.99 seconds
      memory              11310.40k
      OS Memory           16932.00k

Hai.kuo2

      real time           5:20.00
      user cpu time       4:56.76
      system cpu time     3.47 seconds
      memory              11312.81k
      OS Memory           16932.00k

Hai.kuo3

This ran for an hour and a half, so I gave up and cancelled it. I then tried it on a small (total 3,000 good and bad keywords) file, and it ran to completion in 1:14.

PGStats2

      real time           6:13.38
      user cpu time       5:22.26
      system cpu time     6.11 seconds
      memory              11313.37k
      OS Memory           16932.00k

Ksharp1

      real time           6:16.54
      user cpu time       6:05.57
      system cpu time     3.97 seconds
      memory              11259.78k
      OS Memory           16676.00k

Conclusions:

1. Don't use SQL for a problem like this.

2. In terms of finding the words in the sentences, two approaches were used: scan and prx. Duration seems very similar, but prx seems to consume twice the "system CPU time". Is this important? Of course, with a real world problem, prx might be the only way to accomplish the subtle destringing that would be necessary.

3. To manage the keywords, we had three approaches: hash, informat, and array. The array approach seems to have broken down at high volumes, while the hash and informat options came through with flying colours. Informat might be a little faster, but I think we'd need to go to yet higher volumes to try to distinguish them.

Other notes:

These test were run on my Toshiba laptop, an I5 @2.5GHz, 6GB installed memory, Win 7 64 bit.

The data used was a set of Toronto restaurant reviews, pulled off of the web. On the large tests, the raw data was reproduced 100 times, to end up with 263,360,800 words, and 1,405,426,300 characters.

On the small keyword tests I used 320 keywords (187 good, 133 bad). On the big keyword tests I used 17,346 keywords (10136 good, 7210 bad).

I'll keep the data for a week, in case anybody wants anything tested.

Final comments:

Thank you all for providing such wonderful illustrations of the different ways that SAS can solve a real-world problem. I've learned a tremendous amount from this (in addition to never saying "I'll do that" again), and I know that this knowledge will be of great value in the future.

I'm considering writing a paper about this for SGF or some other venue. If any of you would not like me to use your software, please reply in the comments. On the other hand, if any of you would like to be listed as co-authors, also reply to that effect, and we'll figure it out offline.

Tom

Re: Scanning an Observation for a Word within a Variable

TomKari — Fri, 19 Jul 2013 21:33:43 GMT

To @sucksatsas

The results are in from the performance testing. Please see the above post.

Thanks,

Tom

Re: Scanning an Observation for a Word within a Variable

Haikuo — Fri, 19 Jul 2013 21:42:18 GMT

Thank you very much, Tom, for the tremendous efforts you have committed. Feel free to use my code in your paper.

Haikuo

Re: Scanning an Observation for a Word within a Variable

PGStats — Fri, 19 Jul 2013 21:58:47 GMT

Wow Tom, That's what I call serious testing! Thanks a lot. Feel free to use my code.

Re: Scanning an Observation for a Word within a Variable

Ksharp — Mon, 22 Jul 2013 02:40:07 GMT

Maybe I should get my code better.

Feel free to use my code in your paper , Tom. You know my e-mail.

Ksharp