topic Frequency Count of a String in SAS Programming

Frequency Count of a String

JS — Mon, 27 Jun 2016 22:13:36 GMT

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

Re: Frequency Count of a String

Astounding — Tue, 28 Jun 2016 00:49:11 GMT

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;

Re: Frequency Count of a String

Jagadishkatam — Tue, 28 Jun 2016 01:06:29 GMT

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;