How to assign the result from %macro to a macro variable

Question

I have data set with probabilities to purchase a particular product per observation. Here is an example:

DATA probabilities; 
INPUT id P_prod1 P_prod2 P_prod3 ; 
DATALINES; 
1 0.02 0.5 0.32
2 0.6 0.08 0.12
3 0.8 0.34 0.001
; 

I need to calculate the median for each product. Here's how I do that:

%macro get_median (product);
proc means data=probabilities median;
var &product ;
output out=median_data (drop=_type _freq_) median=median;
run;
%mend;

At this point I can get the median for each product by calling

%get_median(P_product1);

Now, the last thing that I want to do is to assign the numeric result for the median to a macro variable. My best guess for how to do that would be something like:

%let med_P_prod1=%get_median(P_prod1);

but unfortunately that does not work.

Can someone help, please?

Cheers!

Answer 1

The simplest solution is to define a %global macro variable and set the let statement to the numeric result inside the macro.

%macro get_median (product);
proc means data=probabilities median;
var &product ;
output out=median_data (drop=_type _freq_) median=median;
run;
%global macroresult;
proc sql;
select median into :macroresult separated by ' ' from median_data;
quit;
%mend;

(That SQL statement is equivalent to LET in that it defines a macro variable, but it is better at getting results from data.)

I'd also recommend just using the dataset in your code rather than putting the value in a macro variable.