Many thanks TOM, Final i got the results with this approach. %macro mtst; %let trtid1=ID001; %let trtid2=ID004; %let trtid3=ID001a; %let trtcnt=3; %let trtlist=ID001,ID004,ID001a; proc sort data=trtdef out=test; by trtid; where trim(left(trtid)) in ( %do i=1 %to &trtcnt; "&&trtid&i" %end; ); run; data test1; length _cnt 8. _kl $200.; set test; if index(codes,'|') then do; _cnt=countw(codes,'|'); do _i=1 to _cnt; _kl=scan(codes,_i,'|'); output; end; end; else do; _kl=codes; output; end; run; proc sort data=test1; by trtid; run; data %do i=1 %to &trtcnt; trtid&i(keep=&&trtid&i) %end; ; set test1; %do i=1 %to &trtcnt; length &&trtid&i $200.; if trim(left(trtid))="&&trtid&i" then do; &&trtid&i=_kl; output trtid&i; end; %end; run; proc sql noprint; create table test2 as select %do i=1 %to &trtcnt-1; &&trtid&i, %end; &&trtid&i from %do j=1 %to &trtcnt-1; trtid&j, %end; trtid&j; quit; %mend mtst; %mtst; Thanks for all your support to get this done TOM. Thanks DeeN
... View more