CODEWITHSUNDEEP
sas
Andy
Andy

Reputation: 7

How to Call a Value Computed in a Macro?

I need to create a "moving median" instead of moving average on a field called OrderSize. So I wrote a macro that takes the current observation and the previous 99 into a temporary dataset called Holding, sorts it, and takes the 50th value as the median. The macro works fine but I can't retrieve &MedValue. from inside the macro. Is there another way to do this? Also, how do you write a if-then-else block spanning multiple lines?

%macro FindMedian(RawData, RowNum);
    data Holding; set &RawData.; where &RowNum.-99 <= RowNum and RowNum <= &RowNum.; run;
    proc sort data=Holding out=Holding; by OrderSize; run;
    data Holding; set Holding; drop RowNum; NewRow = _n_; run;
    proc sql; select OrderSize into :MedValue from Holding where NewRow = 50; quit;
%mend;

data CalculateMedian;
    set RawData;
    if RowNum > 100 then %FindMedian(RawData, RowNum);
    if RowNum > 100 then MedOrderSize = &MedValue.;
    else MedOrderSize = 0;
run;

Upvotes: 0

Views: 37

Answers (1)

Tom
Tom

Reputation: 51566

You are calling a macro that generates multiple steps in the middle of a data step. Once the macro processor has generated those code statements you are effectively running this program:

data CalculateMedian;
set RawData;
if RowNum > 100 then  data Holding; 
set &RawData.; 
where &RowNum.-99 <= RowNum and RowNum <= &RowNum.; 
run;

proc sort data=Holding out=Holding; by OrderSize; run;

Which should produce an error because "data Holding" is not a valid statement to appear in the THEN clause of an IF statement.

Just use an array to store the rolling values. Then you can use the MEDIAN() function to find the median of those values.

data CalculateMedian;
  set RawData;
  array roll [0:99] _temporary_;
  roll[mod(_n_,100)] = OrderSize ;
  MedOrderSize = median(of roll[*]);
run;

Upvotes: 1

Related Questions