Here's another way, it involves more steps than Reeza as it runs the transpose and then does by processing at the end to get what you need (just remove the Where= in the proc transpose to get all CustomerID instead of just 456). Hope this helps! data start; length CustomerID $10 Event_List $50; input CustomerID $ Event_List $; datalines; 123 10,12,100,110,121 456 100,117,121,123,124,000 786 181,444,656 ; run; data merge_data; length Event_list $10 Event_Char $25; input Event_List $ Event_Char $; datalines; 100 Click 117 Click2 121 Buy 123 Order 124 Click Out 000 Exit ; run; data count; set start; count=countc(Event_List,',')+1; run; proc sql noprint; select max(count) into: max from count; quit; %macro split; data separate; set start; %do i=1 %to &max; var&i=scan(Event_List,&i,","); %end; run; proc transpose data=separate out=transposed (drop=_name_) prefix=Event_List; where CustomerID='456'; var %do t=1 %to &max; var&t %end;; by CustomerID; run; %mend; %split data num_separate; set transposed; Event_List=input(Event_List1,6.); run; proc sql; create table separate_merge as select A.CustomerID, B.Event_Char as Event_List from num_separate as A inner join merge_data as B on (A.Event_List1=B.Event_List); quit; data final (drop=Event_List); retain CustomerID Event_Path; set separate_merge; format Event_Path $50.; by CustomerID; Event_Path=catx(", ",Event_Path,Event_List); If Last.CustomerID then output; run;
... View more