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

Here is a solution that doesn't involve a DOW loop: proc format ; value formula low-15=0 16-40=1 41-70=2 71-100=3 101-140=4 141-175=5 176-225=6 226-300=7 301-high=8 ; run; data have; input ID Email $ type $ edate mmddyy10. ; format edate mmddyy10.; datalines; 1 1 rdate 05/20/2015 1 2 rdate 05/21/2015 1 3 Sdate 10/05/2015 1 4 Sdate 11/09/2015 1 5 Sdate 12/11/2015 2 1 rdate 06/21/2015 2 2 rdate 07/05/2015 2 3 rdate 07/09/2015 2 4 Sdate 06/21/2016 2 5 Sdate 07/05/2016 2 6 Sdate 07/09/2016 ; data want(keep=id newEmail type edate rename=newEmail=Email); retain lastRdate; set have; by id type notsorted; if first.id then do;     call missing(lastrdate,firstSdate);     newEmail = 0;     end; if type="rdate" and last.type then lastRdate = edate; if type="Sdate" and first.type and not missing(lastRdate) then do;     firstSdate = edate;     nbDates = input(put(intck("DAY",lastRdate,firstSdate),formula.),best.);     dayStep = ceil(intck("DAY",lastRdate,firstSdate) / (nbDates+1));     type = "Formula";     do i = 1 to nbDates;         edate = intnx('DAY', lastRdate, i*dayStep);         newEmail + 1;         output;         end;     type = "Sdate";     edate = firstSdate;     end; newEmail + 1; output; run; PG ... View more