here is what i did      proc sort data=have ; by id Region; run; /* this step to determine who how many logos for each ID */ data num_of_logos ; set have ; by id region; N+1; If first.region then N=1; if n <= put(regionquantity,1.) ; run; proc sort data=num_of_logos ; by id Priority;run;   /* transpose the rows to columns starting from block4 */ DATA trans_col; ARRAY Block(24) $ 50 ; do i = 1 to 24; SET num_of_logos; BY id Priority ; IF region='P' then do; i=3; Block(i+N)=logo; end; IF region='B' then do; i=6; Block(i+N)=logo; end; if region='S' then do; i=9; Block(i+n)=logo; end; if region='T' then do; i=12; Block(i+n)=logo; end; IF last.id THEN RETURN; END; RUN; proc sort data=have out=drivertable nodupkey; by logo region priority ;run; %MACRO FORMATING; data forformat; set drivertable; array vars $ vdata1-vdata5; do over vars; if vars ='%%name%%' then vars=strip(Fname); run; %MEND FORMATING; %formating; %MACRO FORMATS; %do j = 1 %to 5; data Block&j (keep=fmtname start label type); set forformat ; retain fmtname "$vdata&j._" type 'c'; rename logo=start vdata&j=label; run; proc format library=work cntlin=block&j;run; %end; %MEND FORMATS; %formats; %MACRO VDATA; data vdata; set trans_col; /* length block4data1 $100. block4data2 $100. block4data3 $100. block4data4 $100. block4data5 $100. ;*/ %do i = 1 %to 24; /*message priority */ %do j = 1 %to 5; Length Block&i.data&j $100.; Block&i.data&j=put(Block&i,$vdata&j._.); %end; %end; drop i n Priority Region RegionQuantity n vdata1 vdata2 vdata3 vdata4 Vdata5 ; run; %MEND VDATA; %vdata; ... View more