Can I suggest you post some test data and required output.  Its quite difficult to guess the circumstances.  At a guess you could get those directors with six years by: proc sql;      select     DIRECTORID      into         :MYLIST separated by '","'      from        (select     distinct DIRECTORID,                                     count(YEAR) as CNT                      from         HAVE)      where     CNT = 6; quit; This list could then be used to filter tha dataset, data want;      set have;      where directorid in ("&MYLIST."); run; ... View more

This means less than, i.e. if (first.id and last.id) is less than 1. Sybols are: < less than > greater than <= less than or equal to >= greater than or equal to There are several others in the SAS help. ... View more

Well, the only other thing I can think of is array processing with by groups.  So: data want;      set have;      by id;      array a{20};      array b{20};      ...      process result into next available array based on spec for a or b etc.      if last.id then output; run; ... View more

Sorry, test data and output would probably clarify somewhat.  You want something like: id     a1 a2 a3...a20 b1 b2...b20 c1.. If so then transpose data for the A variables, then the B variables, then the C variables and merge them together. ... View more

Hi, Sorry for the late reply, I had this on a SAS session which crashed yesterday when I was working on it, so am posting this late (note that you could replace the yes_results and no_results with a multi level array, but I kept It like this for simplicity: data have; attrib q1 q2 q3 q4 format=$10.; infile cards dlm=','; input q1 $ q2 $ q3 $ q4 $; cards; yes,no,yes,yes no,yes,yes,yes yes,yes,no,no ; run; data want (drop=i);   set have end=last;   attrib quarter format=best. yes no format=$20.;   array q{4};   array yes_results{4} (0,0,0,0);   array no_results{4} (0,0,0,0);   do i=1 to 4;     if q{i}="yes" then yes_results{i}=yes_results{i}+1;     if q{i}="no" then no_results{i}=no_results{i}+1;   end;   if last then do;     do i=1 to 4;       quarter=i;       yes="%"||strip(put( (yes_results{i} / 4) * 100,best.));       no="%"||strip(put( (no_results{i} / 4) * 100,best.));       output;     end;   end; run; ... View more

Hi, Could you clarify what it is exactly that is "different" each time you run it.  Is it the style, report widths/format, data containd therein etc. I would assume, that your style is fixed on the ods html line, though I cant see that from you code snippet. For report style format, you are passing in variables from somewhere and using that to assign formats?  What are these variables, do they change.  If you want each report to be the same then fix the define steps so they remain constant, apply the formats if you must in the data itself not the report statement - e.g. create a temporary table. I would also suggest that Astounding is correct to pick up on the most_recent_num macro variable.  Could I suggest that this should be done outside of the macro itself creating a temporary dataset which is then passed into the macro.  This is called encapsulation, keeping all of the necessary items within their own scope, i.e. if your macro is copied somewhere else then it will fail as it requires that macro variable. So: %macro reports(var,var_name); ods noptitle; proc report data =TEMP  nowindows completecols  ; column &var. &var.=&var.2 &var.=&var.3 &var.=&var.4 &var.=&var.5 mail_month, (mailed mailed=col_pct); define &var. /"&var_name." group width=10; define &var.2 /"Points" group width=10; define &var.3 /"Dev Volume" group width=10; define &var.4 /"Dev Pct" group width=10; define &var.5 /"Dev RR" group width=10; define mail_month /'' across ; define mailed /"Volume" analysis width=10; define Col_pct /"Col %" analysis pctsum ; title ; run; %mend; data TEMP;      set HAVE;      where end_of_renewal_month>=intnx('month',&most_recent_num.,-12,'end');      format ... ...;      ... run; /* At this point I can check that temp has the data I want, is in the format I want. %reports(av_age_band,Age Band); /* This will then just create the report definition with the data passed in.  It has no need to know most_recent_num, and assumes that the dataset will always be temp*/ ... View more

You could create a template dataset as KurtBremser has stated.  Its probably easy enough.  Alternatively you could think about it this way, create and empty results table, then using data _null_ with a do loop, generate insert statements for each year: proc sql;      create table WANT      (               YEAR num,           NOI num      ); quit; data _null_;      do I=0 to 13;           call execute('proc sql;                                    insert into WANT                                    set     YEAR='||strip(put(2000+i,best.))||',                                              NOI=(select count(IND) from HAVE where YEAR='||strip(put(2000+i,best.))||' and INCOME > 10000);                                quit;');      end; run; ... View more

Have you tried opening with Excel and saving the output.  Excel has a pretty decent XML/HTML parser present, and then you could save the output in delimited format to read into SAS.  Its dependant on your HTML data of course. ... View more