Hi, I am sure I am missing something simple, but I have been spinning my wheels for hours on this and just don't know where to turn besides a forum. This is on SAS Enterprise guide 8.2 I am trying to create a table that will be used to make an MRL plot. Here is my code: %macro calculate_avg(threshold, data_table);
data _temp;
set &data_table(keep=Trended_Loss);
where trended_loss > &threshold;
excess = trended_loss - &threshold;
run;
proc sql noprint;
select avg(excess)
into :AAT
from _temp;
quit;
%mend calculate_avg;
data work.results;
set work.percentiles;
call execute(cats('%calculate_avg(', Percentile_threshold, ',work.Trended)'));
Mean_Excess_Loss = &AAT;
run; work.percentiles is a table is derived from work.Trended, containing the percentiles from 90 to 99.9 with a .1 step. What I am trying to do is pass the threshold based on the percentile into the macro and estimate the average above the threshold using the _temp table and the proc sql statement Since the table contains 100 different thresholds, I initially tried to make a do loop but that didn't seem to work out... If I remove noprint and comment out the Mean_Excess_loss line, I can see the results of the test at various percentiles, so the logic is sound. I just can't seem to get the value being estimated out of the macro and back into my results table. Here is the SAS log: 107 Mean_Excess_Loss = &AAT;
_
22
WARNING: Apparent symbolic reference AAT not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, INPUT, PUT.
108 run; Any insights you have will be extremely helpful, thank you.
... View more