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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.