I have a relatively simple dataset: basically it's just a list of variable names and their numeric values. I'm trying to find a good macro/approach to adding to this dataset new entries whose values are multiples of specified variables in the database.
Name | Value
Strength = 9
Dexterity = 4
Constitution = 15
Intelligence = 12
I want to be able to create "New Variable" with a value of Strength x Dexterity (or whatever)
The situation might seem simple enough, but both the values and the variables I'm picking are going to change a lot, and with many iterations, and it would be much easier to dynamically calculate the new variables rather than entering them manually in a data step. A macro seems to make the most sense, but I'm not sure how I can pass varying numbers of parameters to such a macro (e.g. sometimes I only want 2 variables multiplied, but sometimes it will be 3 or more).
Any thoughts on this? (Even a way to use a dynamic number of parameters in a macro would be a huge help!)
For your formulas, will they need be adjustable at the observation level or at the DATA step level? It makes quite a bit of difference on the complexity of the coding. It is likely that the interrelationship of these variables will also be changing, how is the relationship defined/controlled?
Here is an alternative solution, although this macro probably still needs some work to make it more robust.
length Name $100;
input Name $ Value;
proc sql noprint;
select distinct value into: expression separated by '*'
where find("&names", trim(name)) > 0;
if 0 then modify test;
name = tranwrd("&names", ' ', ' x ');
value = %sysevalf(&expression);
If you invoke this macro using %multiply(Strength Intelligence) and print the test data set, here are the results:
Within the macro, &newvar resolves to a variable in the dataset (good) and the first scan statement resolves to a value from a string of values (also good). But the second scan statement is returning a string value when I want SAS to interpret it as a variable name. What is the magic word to do such a thing? I have messed with quotes and ampersands but can't seem to get it to work.
For reference, here is my macro code. The basic idea is that it generates a list of desired values (or coefficients) from one data set, then in the second data set multiplies all the matching variables by their coefficients and adds them together into a single variable. It doesn't quite work yet, but I think I could make it to work if I understood how I can force scan to see a variable name instead of a string. The underlined part is what I'm referencing in the previous post.
%macro freezer (data=, coefficients=, varcol=, valcol=, variables=, combined=, outdata=);
/* This is pretty much polingjw's code and puts a list of desired values separated by spaces into "expression" */
proc sql noprint;
select distinct &valcol into: expression separated by ' '
where find("&variables", trim(&varcol)) > 0;
/* end polingjw's code */
/* set out data set to data set */
data &outdata; set &data;
&combined = 0;
i = 1;
/* create new variable that multipies desired variables by their matching coefficients */
data &outdata; set &outdata;
do until(scan(expression, i) = ' '); &combined = &combined + scan(expression, i) * scan("&variables", i); i+1;
drop i expression;