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
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.