SAS Call SYMPUT - one macro variable, two values? (conditional)

Question

I would like to create one macro called 'currency_rate' which calls the correct value depending on the conditions stipulated (either 'new_rate' or static value of 1.5):

%macro MONEY;
%Do i=1 %to 5;

data get_currency_&i (keep=month code new_rate currency_rate);
set Table1;   
if month = &i and  code = 'USD' then currency_rate=new_rate;
else  currency_rate=1.5;
run;

data _null_;
set get_currency_&i;
if month = &i and  code = 'USD' then currency_rate=new_rate;
else  currency_rate=1.5;
call symput ('currency_rate', ???);
run;

%End;

%mend MONEY;

%MONEY

I am happy with the do loop and first data step. It is the call symput I am stuck on. Is call symput the correct function to use, to assign two possible values to one macro?

A snippet example of the way I will be using 'currency_rate' in a proc sql:

    t1.income/&currency_rate.          

I am a beginner level SAS user, any guidance would be great!

Thanks

Answer 1

Let's simulate your case. Suppose we have 3 datasets, as shown below -

data get_currency_1;
    input month code $ new_rate currency_rate;
cards;
1 USD 2 2
2 CHF 2 1.5
3 GBP 1 1.5
;
data get_currency_2;
    input month code $ new_rate currency_rate;
cards;
1 USD 3 1.5
2 USD 4 4
3 JPY 0.5 1.5
;
data get_currency_3;
    input month code $ new_rate currency_rate;
cards;
1 USD 1 1.5
2 USD 3 1.5
3 USD 2.5 2.5
;

Now, let's run your code where we assign a value to currency_rate.

Let i=1 So, the dataset get_currency_1 will be accessed. As we run the step, each and every row will be accessed and the value of currency_rate will be assigned to the macro variable currency_rate and this iteration will continue till the end of the data step. At this time, the last value will be of currency_rate will be the final value of macro variable currency_rate because beyond that the step ends.

%let i=1;   /*Let's assign 1 to i*/
data _null_;
    set get_currency_&i;
    if month = &i and  code = 'USD' then currency_rate=new_rate;
    else  currency_rate=1.5;
    call symput ('currency_rate', currency_rate);
run;
%put Currency rate is: &currency_rate;
    Currency rate is:          1.5

Let i=3:

%let i=3;   /*Let's assign 3 to i*/
data _null_;
    set get_currency_&i;
    if month = &i and  code = 'USD' then currency_rate=new_rate;
    else  currency_rate=1.5;
    call symput ('currency_rate', currency_rate);
run;
%put Currency rate is: &currency_rate;
    Currency rate is:          2.5

You cannot have multiple values on one macro variable.

Answer 2

You say you are a beginner, so the best course of action is to avoid macro programming at this point. You would be better served learning about where, merge (or join) and by statements.

You state you will need to use a currency_rate in a statement such as

t1.income / &currency_rate.

The t1. to me suggests t1 is an alias in a SQL join and thus the far more likely scenario is that you need to left join table t1 that contains incomes with table1 (call it monthly_datum) that contains the monthly currency rates.

select
  t1.income / coaslesce(monthly_datum.currency_rates,1.5)
, …
from
  income_data as t1
left join
  monthly_datum
on t1.month = monthly_datum.month 

The rate of 1.5 would be used when the income is associated with a month that is not present in monthly_datum.

Answer 3

A macro variable can only hold a single value. Since you're only ever assigning a single value, you can easily use CALL SYMPUTX().

 call symputx('currency_rate', currency_rate);

But if your data has more than one row, then the value will be the last value set in the data set.