topic Re: Most popular value from a text string in SAS Procedures

Most popular value from a text string

mlance — Mon, 19 May 2014 11:46:21 GMT

Hi,

I'm trying to pull out the most popular value from a text string and create a new variable that tells me what it is.

e.g.

'AAAABBBCCD' would give me a new variable with the value of 'A' in this instance.

Can anyone help please?

Thanks in advance.

Re: Most popular value from a text string

jwillis — Mon, 19 May 2014 12:03:48 GMT

Dear Miance,

I do not know how many rows of data you are working with; the length of the source variable; nor whether you want the new variable on the same row as the original variable. Are the values in the variable limited to the 26 letters in the alphabet?

Re: Most popular value from a text string

RW9 — Mon, 19 May 2014 12:10:11 GMT

Please clarify the requirements. There are many string functions, as can be found in the docs: http://support.sas.com/publishing/pubcat/chaps/59343.pdf

However its dependant on your specific scenario. A simple calculation on the A or B or C etc. is relatively straight-forward, however its probably unlikely you want that.

Re: Most popular value from a text string

Amir — Mon, 19 May 2014 12:42:46 GMT

Hi,

The following gives "A":

data _null_;

length want $1;

have='AAAABBBCCD';

text=have;

max=0;

do until(lengthn(text)=0);

achar=substr(text,1,1);

tally=countc(trim(text),achar);

if tally gt max then

do;

want=achar;

max=tally;

end;

text=compress(text,achar);

end;

put want=;

run;

Regards,

Amir.

Re: Most popular value from a text string

data_null__ — Mon, 19 May 2014 13:14:55 GMT

I had the pretty much the same idea as you with slightly different functions. I made a list for ties.

data _null_; 
   input s $char32.; 
   ws=compress(s,' '); *don't count spaces;
   do while(not missing(ws));
      l=first(ws);
      c=countc(ws,l,'TI'); *add I to ignore case;
      m=max(m,c);
      length p $32; 
      p=ifc(c eq m,catt(p,l),p);
      ws=compress(ws,l,'I'); *add I to ignore case;
      end; 
   put 'NOTE: Most popular character(s) :' p 'frequency:' m;
   cards; 
AA  aaBBBCCD
CcC  AA  AABBBCCD
lkdjLabnejndkijdidnd
   run;

Re: Most popular value from a text string

Ksharp — Mon, 19 May 2014 13:20:39 GMT

data temp;
 set sashelp.class(keep=name);
 do i=1 to length(name);
  char=upcase(char(name,i));
  output;
 end;
 drop i;
run;
proc freq data=temp order=freq noprint;
by name;
tables char /out=temp1(drop=percent) nopercent ;
run;
data want;
 set temp1;
 by name;
 if first.name;
run;

Xia Keshan

Re: Most popular value from a text string

mlance — Mon, 19 May 2014 15:52:29 GMT

Thanks all for your help, I have a solution that works now.

Re: Most popular value from a text string

mlance — Mon, 19 May 2014 15:53:07 GMT

Thanks very much Amir this works how I wanted it to.

Re: Most popular value from a text string

data_null__ — Mon, 19 May 2014 16:04:33 GMT

I'm wondering why since gave you the program that "works how you wanted" that you did not mark the reply correct but merely helpful which leaves your question "Not Answered".

Re: Most popular value from a text string

mlance — Tue, 20 May 2014 07:37:42 GMT

Updated.