Thank you for the solution I have one more doubt: If a pattern contains the consecutive keyword "one" then I want it to be merged and considered as single keyword "one" but get the average of total number of times "one" appeared. (explained in the example) For example: Clicks Group TotalTime Time
A 1 5
B 1 3
C 1 2
one 1 10 0
D 2 6
E 2 6
F 2 12 0
A 3 3
B 3 5
C 3 4
one 3 6
one 3 2
one 3 20 0
H 4 10
I 4 8
J 4 18 0 output expected: Pattern Freq Time Average Number of Clicks
ABCone 2 A-4,B-4,C-3,one-2 15 4
DEF 1 D-6,E-6,F-0 12 3
HIJ 1 H-10,I-8,J-0 18 3 Output which I received from the code is not right so can you help me in correcting where I am going wrong ? code: data have2 / view=have2; length pattern $30; pattern = '|'; * prepare for bounded token search via INDEX(); do _n_ = 1 by 1 until (last.group); set have; by group; * use this line if all items in group are known to be distinct ; * pattern = cats(pattern,clicks); * track observed clicks by searching the growing pattern of the group; bounded_token = cats( '|', clicks, '|' ); if index (pattern, trim(bounded_token) ) = 0 then pattern = cats (pattern, clicks, '|'); end; if length (pattern) = lengthc(pattern) then do; put 'WARNING: pattern needs more length'; stop; end; * remove token bounders; pattern = compress(pattern,'|'); do _n_ = 1 to _n_; set have; output; end; run; proc means noprint data=have2; class pattern clicks; var time; ways 2; output out=have_means mean=mean ; run; data want (keep=pattern time_summary _freq_); do until (last.pattern); set have_means; by pattern; length time_summary $100; time_summary = catx(',',time_summary,catx('-',clicks,mean)); end; run;
... View more