Using a Macro Variable inside a Datastep

[endofline]

Hi I'm having some trouble calling in a variable within my dataset, within a macro. 

My data is structured as such

Obs      RefYr    DollarAmt

1          2010            100

2          2010             200

3          2011             100

4         2012              100

5         2013              100

I'm trying to use a macro and a do loop, to select the correct constant to mulitple my dollar amount, depending on the year. Here is the code I have currently:

%let const_2010=1.25;

%let const_2011=1.30;

%let const_2012=1.40;

%let const_2013=1.50;

%macro pricestd (yr);
data test_1;
set  test;

%do i=2010 %to &yr;

      %if refyr=&i %then %do;
         stdpayment=(&&const_&i*dollaramount);
       %end;
%end;
run;

%mend; 

Here is my log:

SYMBOLGEN: Macro variable YR resolves to 2010
MLOGIC(PRICESTD): %DO loop beginning; index variable I; start value is 2010; stop value is 2010; by value is 1.
SYMBOLGEN: Macro variable I resolves to 2010
MLOGIC(PRICESTD): %IF condition refyr=&i is FALSE
MLOGIC(PRICESTD): %DO loop index variable I is now 2011; loop will not iterate again.
MPRINT(PRICESTD): run;

I'm not sure if this is the best way to go about trying to solve my problem. I'm also not sure why the logic is turning out false. 

Any insight would be great! Thanks! 

[ballardw · Accepted solution]

You run into a lot of problems trying to make macro variables behave like datastep. No need for a macro at all. Since you are looking up a value based on an existing value in your data, refyr, you can use that to reference the values of a temporary array.

data have;
   input refyr dollaramt;
datalines;
2010  100
2010  200
2011  100
2012  100
2013  100
;
run;

data want;
   set have;
   array const {2010:2013} _temporary_ (1.25 , 1.30 , 1.40, 1.50);
   stdpayment = const[refyr] * dollaramt;
run;

Note that your examples used two spellings for dollaramt / dollaramount. I picked the first one.

If you need a much longer list you would be better off creating a data set with the key value (refyr) and the needed value(s) and using Proc SQL to join and use the other variable(s).

[endofline]

Thanks! That worked. I'm not too familiar with arrays. How would you do with is sql?

[Astounding]

To  do this in SQL, you would begin by creating a separate data set with just two fields:  REFYR and the value that matches that REFYR such as 1.25, 1.30, etc.

Then you would join the data sets by REFYR.

[ballardw]

---

@endofline wrote:

Thanks! That worked. I'm not too familiar with arrays. How would you do with is sql?

---

It will be very beneficial to learn arrays as a general tool for data manipulation. It is the main tool/structure for doing the same operation involving multiple variables.

Here is a worked SQL approach:

data have;
   input refyr dollaramt;
datalines;
2010  100
2010  200
2011  100
2012  100
2013  100
;
run;

data lookup;
   input refyr multiplier;
datalines;
2010 1.25
2011 1.30
2012 1.40
2013 1.50
;
run;
   
proc sql;
   create table want as
   select have.refyr, have.dollaramt,
          have.dollaramt*lookup.multiplier as stdpayment
   from have left join lookup
        on have.refyr=lookup.refyr
   ;
quit;

Note the use of datasetname.variable to reference which value you want with the same name. I'll leave it to you to look up ALIAS useage to shorten the typing especially if you are dealing with datasets from multiple libraries where you start referencing libname.datasetname.variablename.

Your lookup data set could have multiple other variables that are set for the same key value. From context possibilities could be commision rate, tax rate or whatever makes sense. Notice that we used the multiplier but did not explicitly keep it on the Select statement so that variable does not appear in the output.

[Tom]

Your condition is always going to be false because the macro variable I takes on values like '2010', '2011' etc so it can never be equal to the the string of characters 'refyr' that you are comparing it to with this statement.

%if refyr=&i %then %do;

Use normal SAS language statements to act on your data.  You could use macro logic to generate the SAS code, but you can't use it to examine your data.

[RW9]

Another approach apart from the array one given above, is to use the basica functionality of datasets and merging.  Instead of putting data into macro variables, put data into datasets (clue in the name there).  This data can then simply be merged on to be used:

data consts;
  year=2010; rate=1.25;
  year=2011; rate=1.30;
...
run;

proc sql;
  create table WANT as
  select  A.*,
          B.RATE,
          A.DOLLARAMT * B.RATE as ACTUAL
  from    HAVE A
  left join CONSTS B
  on      A.REFYR=B.YEAR;
quit;

The above is just an example, but is there to illustrate how simple it is to merge two datasets to get an outcome, no need for looping, macro variables, messy code etc.