topic Re: Dynamic macro question in SAS Programming

Dynamic macro question

GVeers — Tue, 14 Dec 2010 22:14:38 GMT

Hi all,

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.

E.g.

Name | Value
Strength = 9
Dexterity = 4
Constitution = 15
Intelligence = 12

etc.

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!)

Re: Dynamic macro question

art297 — Wed, 15 Dec 2010 02:04:31 GMT

You could always just pass a string and parse if with %scan.

Art

Re: Dynamic macro question

ArtC — Wed, 15 Dec 2010 02:58:35 GMT

For a varying number of macro parameters you can also take advantage of the /PARMBUFF option on the %MACRO statement. You will still however need to do some parsing, probably with %SCAN or %QSCAN.

Re: Dynamic macro question

ArtC — Wed, 15 Dec 2010 03:02:11 GMT

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?

Re: Dynamic macro question

SPR — Wed, 15 Dec 2010 16:29:57 GMT

Hello GVeers,

If I understand you correctly then this is a solution:
[pre]
data i;
length Name $100;
input Name $ Value;
datalines;
Strength 9
Dexterity 4
Constitution 15
Intelligence 12
;
run;
%macro a(operation);
%let i=0;
%do %until (&&v&i = );
%let i=%EVAL(&i+1);
%let v&i=%SCAN(&operation,&i,"*");
%end;
%let n=%EVAL(&i-1);
%put v1=&v1 v&n=&&v&n;
data t;
merge
%let nv=;
%let vv=;
%do i=1 %to &n;
%let nv=&nv._&&v&i;
%let vv=&vv.*val&i;
i (rename=(name=nam&i value=val&i) where=(UPCASE(nam&i)=UPCASE("&&v&i")))
%end;
;
%let nv=%SUBSTR(&nv,2);
%let vv=%SUBSTR(&vv,2);
%put nv=&nv vv=&vv;
name="&nv";
value=&vv;
keep name value;
run;
data r;
set i t;
run;
%mend a;
%a(Dexterity*Constitution*Strength)
[/pre]
Sincerely,
SPR

Re: Dynamic macro question

polingjw — Wed, 15 Dec 2010 18:51:35 GMT

Here is an alternative solution, although this macro probably still needs some work to make it more robust.

[pre]
data test;
length Name $100;
input Name $ Value;
datalines;
Strength 9
Dexterity 4
Constitution 15
Intelligence 12
;

%macro multiply(names);
proc sql noprint;
select distinct value into: expression separated by '*'
from test
where find("&names", trim(name)) > 0;
quit;
data test;
if 0 then modify test;
name = tranwrd("&names", ' ', ' x ');
value = %sysevalf(&expression);
output;
run;
%mend;

[/pre]

If you invoke this macro using %multiply(Strength Intelligence) and print the test data set, here are the results:

[pre]
Obs Name Value

1 Strength 9
2 Dexterity 4
3 Constitution 15
4 Intelligence 12
5 Strength x Intelligence 108
[/pre]

Re: Dynamic macro question

Ksharp — Thu, 16 Dec 2010 08:58:38 GMT

Hi.polingjw
The code can change somewhat.

Sorry.I am wrong.

Ksharp Message was edited by: Ksharp

Re: Dynamic macro question

GVeers — Thu, 16 Dec 2010 19:01:38 GMT

Thanks for everyone's help. I realized after my post that I over-simplified my problem, but nonetheless I am most of the way there thanks to the code above.

The last thing I'm now trying to figure out is how to use the scan function to return a variable name (rather than a string value) within my macro. My snippet of code looks something like this:

&newvar = scan("&string_vals", i) * scan(&string_vars, i);

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.

Re: Dynamic macro question

GVeers — Thu, 16 Dec 2010 19:08:17 GMT

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 ' '
from &coefficients
where find("&variables", trim(&varcol)) > 0;
quit;
/* end polingjw's code */

/* set out data set to data set */
data &outdata; set &data;
&combined = 0;
i = 1;
run;
/* 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;
output;
end;
drop i expression;
run;
%mend freezer;

Re: Dynamic macro question

ArtC — Thu, 16 Dec 2010 19:17:31 GMT

Almost certainly these will need to be %SCAN or %QSCAN functions and probably inside of a macro loop (making the word number &I).

> &newvar = scan("&string_vals", i) *
> scan(&string_vars, i);

It is a problem of timing. The macro language writes the assignment statement that will be compiled in the DATA step.