DATA Step, Macro, Functions and more

Frequency Count of a String

Reply
Contributor JS
Contributor
Posts: 38

Frequency Count of a String

[ Edited ]

 

I have a set of observations where: when a rule is activated, it simply populated a field as a string with a semi-colon dilineator. The rules can be out of order, some are long, some are short, etc. etc.

 

Is there a way to cross tabulate on a string of texts?

 

Have: 

 

Observation Pass Rule_String
1 1 Rule A;Rule C;Rule D
2 0 Rule C;Rule B
3 1 Rule A;Rule B

Want: 

 

 Rule Count Pass
A 2 2
B 2 1
C 2 1
D 1 1

Super User
Posts: 5,516

Re: Frequency Count of a String

You will need to split them out first, but that's not terribly difficult.  Here's one approach:

 

data split;

set have;

if rule_string >  ' ' then do i=1 to countw(rule_string, ';');

   rule = scan(rule_string, i);

   output;

end;

run;

proc means data=split n sum;

class rule;

var pass;

run;

Trusted Advisor
Posts: 1,137

Re: Frequency Count of a String

data have;
input Observation Pass Rule_String&$20.;
newstring=strip(tranwrd(Rule_String,'Rule',''));
datalines4;
1 1 Rule A;Rule C;Rule D
2 0 Rule C;Rule B
3 1 Rule A;Rule B
;;;;

/*to get the individual records of data*/
data want(where=(string ne ''));
set have;
do i = 1 to countw(newstring);
string=strip(scan(newstring,i,';'));
output;
end;
run;

/*to count the string*/
proc freq data=want noprint;
table string/out=count1;
run;

/*to count the pass*/
proc freq data=want noprint;
where pass ne 0;
table string*pass/out=count2(rename=(count=pass2));
run;

/*final dataset*/
data all(rename=(pass2=pass));
merge count1 count2;
by string;
drop percent pass;
run;
Thanks,
Jag
Ask a Question
Discussion stats
  • 2 replies
  • 274 views
  • 1 like
  • 3 in conversation