topic Re: How to count the frequency like this? in SAS Programming

How to count the frequency like this?

MikeTurner — Mon, 05 Aug 2013 09:55:40 GMT

Dear All,

I'm doing one text mining project.One observation is one string and one indicator. I need to count the frequency of each word in the string and I will get one new dataset with many columns. The name of one column is the word and the content is the the frequency of the word in one observation. The indicator remains unchanged. How to implent this using sas if I have hundreds of words in one string? Thanks.

Have

string	indicator
a b c	1
a c d d a	1
a b c	2
f g g h	2
e f e a m	1

Want

a	b	c	e	d	f	g	h	m	indicator
1	1	1	0	0	0	0	0	0	1
2	0	1	0	2	0	0	0	0	1
1	1	1	0	0	0	0	0	0	2
0	0	0	0	0	1	2	1	0	2
1	0	0	2	0	1	0	0	1	1

Tomi

Re: How to count the frequency like this?

Scott_Mitchell — Mon, 05 Aug 2013 10:53:23 GMT

How about this?

DATA STUFF;

SET HAVE;

A = 0; B = 0; C = 0; D = 0; E = 0; F = 0; G = 0; H = 0; M = 0;

ARRAY VARS A B C D E F G H M;

DO J = 1 TO DIM(VARS);

DO I = 1 TO LENGTH(COMPRESS(STRING));

IF VNAME(VARS{J}) = SCAN(STRING,I) THEN VARS{J} = VARS{J}+INDICATOR;

END;

DROP I J;

RUN;

Re: How to count the frequency like this?

MikeTurner — Mon, 05 Aug 2013 11:06:03 GMT

Thanks. But it will not work if if you have hundreds of words in one string................

Re: How to count the frequency like this?

Scott_Mitchell — Mon, 05 Aug 2013 11:21:46 GMT

You probably should provide a sample that is representative of the data you will be working with.

It appears to work for me without issue for 200 characters.

Re: How to count the frequency like this?

data_null__ — Mon, 05 Aug 2013 12:45:35 GMT

As long as the words are "SAS Names".

data ind;
   input string &$32. indicator;
   id + 1; 
   cards; 
a b c  1
a c d d a  1
a b c  2
f g g h  2
e f e a m  1
;;;;
   run; 
data ind2;
   set ind;
   length word $32; 
   do i = 1 by 1; 
      word = scan(string,i,' ');
      if missing(word) then leave;
      output; 
      end; 
   run; 
proc summary data=ind2 nway; 
   class id indicator word;
   output out=sum(drop=_type_);
   run; 
proc transpose data=sum out=final;
   by id indicator;
   var _freq_;
   id word;
   run; 
proc stdize reponly out=poked missing=0; 
   var _numeric_;
   run;

Re: How to count the frequency like this?

Tom — Mon, 05 Aug 2013 12:45:57 GMT

You will have trouble if your "words" are not valid SAS variable names.

data tall / view=tall ;

row + 1;

set have ;

do i=1 to countw(string,' ');

word = scan(string,i,' ');

output ;

end;

keep row indicator word ;

run;

proc summary data=tall nway ;

by row indicator ;

class word ;

output out=tall_count;

run;

proc transpose data=tall_count out=want (drop=_name_);

by row indicator ;

var _freq_ ;

id word ;

run;

proc print; run;

Re: How to count the frequency like this?

Haikuo — Mon, 05 Aug 2013 14:09:18 GMT

Here is another option using Hash objects.

data have;

input string :&$10. indicator;

cards;

a b c 1

a c d d a 1

a b c 2

f g g h 2

e f e a m 1

;

data _null_;

if _n_=1 then do;

length mvar $ 200;

retain var ' ';/*This would be your maximum length of your individual word*/

declare hash h();

h.definekey('var');

h.definedata('var');

h.definedone();

declare hiter hi('h');

end;

set have end=last;

do i=1 to countw(string);

var=scan(string,i);

rc=h.ref();

end;

if last then do;

do rc=hi.first() by 0 while (rc=0);

mvar=catx(' ',mvar,var);

rc=hi.next();

end;

call symputx('vname',mvar);

end;

run;

data want;

array _var &vname;

set have;

do over _var;

do i=1 to countw(string);

_var+(vname(_var)=scan(string,i));

end;

output;

call missing(of _var(*));

drop string i;

run;

Several things to keep in mind:

1. the max length of macro variable is 64K (65,534) in SAS 9.3, the it works if the total length of all of your distinct words(plus blanks in between) is less than that.

2. the max length of any individual word is 32 char.

3. In current code, case is NOT ignored. It can be easily flipped.

Good luck,

Haikuo

Re: How to count the frequency like this?

MikeTurner — Tue, 06 Aug 2013 01:39:56 GMT

Thanks a lot for all kind reply. Greatly appreciated.

Mike

Re: How to count the frequency like this?

UrvishShah — Tue, 06 Aug 2013 06:25:12 GMT

I know it has been marked as ANSWERED...but still here is my try if you like...

data want;

set have;

i = 1;

str = scan(string,i);

do while(str NE " ");

j + 1;

_str_ = str;

i = i + 1;

str = scan(string,i);

output;

end;

run;

proc sort data = want;

by string indicator;

run;

proc freq data = want noprint;

tables _str_ / out = freq(drop = percent);

by string indicator;

run;

proc transpose data = freq out = want(drop = _name_ _label_);

by string indicator;

var count;

id _str_;

run;

data want(drop = i);

set want;

array temp(*) _numeric_;

do i = 1 to dim(temp);

if temp(i) = . then temp(i) = 0;

end;

run;

-Urvish