Macro executing Proc Report produces different results when ran multiple times

[Sharpshooter]

Hi,

I've created a macro that runs a Proc Report, and i can't understand why i get different results when i run it multiple times. The underlying data does not change, neither do any of the values passed into the macro.

The issue seems to be centered around the formats applied to the data that are used to group the values. Code below and results:

Please help!

Thanks

Adam

/**********************/

%macro reports(var,var_name,points_fmt,dev_rr,dev_vol,dev_pct,char_fmt,num);
ods noptitle;
proc report data =wtf(where=(end_of_renewal_month>=intnx('month',&most_recent_num.,-12,'end')))
      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 format=&char_fmt. exclusive preloadfmt;
define &var.2 /"Points" group format=&points_fmt.;
define &var.3 /"Dev Volume" group format=&dev_vol.;
define &var.4 /"Dev Pct" group format=&dev_pct.;
define &var.5 /"Dev RR" group format=&dev_rr.;
define mail_month /'' across ;
define mailed /"Volume" analysis format=comma8.;
define Col_pct /"Col %" analysis pctsum ;
title ;
run;
%mend;

%reports(av_age_band,Age Band,R_fmt_S_age.,fmt_RR_age.,fmt_R_Dev_age.,fmt_R_Dev_age_ii.,R_age_bd_ii.,01);

[Astounding]

Yes, it could be the formats.  But also notice ...

The macro uses a macro variable that is not passed as a parameter (&MOST_RECENT_NUM).  Somehow, the code that runs before this macro is setting that variable.  If the code that runs before changes, this macro variable might have changed, affecting the records selected.

[Sharpshooter]

I don't think it is the &MOST_RECENT_NUM macro as this is set at the very start of the code and isn't changed. I've stripped teh code back to only include 1 months data and removed the WHERE statement and still get different results each time i run it.

[RW9]

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*/

[Sharpshooter]

Found the solution:

Options NOTHREADS;

There was a note in the log stating "The format width was probably too small for one or more of the values". I'm still running 9.1.3, this bug wasn't fixed until 9.2

Thanks all for your help.