BookmarkSubscribeRSS Feed
GVeers
Calcite | Level 5
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!)
9 REPLIES 9
art297
Opal | Level 21
You could always just pass a string and parse if with %scan.

Art
ArtC
Rhodochrosite | Level 12
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.
ArtC
Rhodochrosite | Level 12
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?
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
polingjw
Quartz | Level 8
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]
Ksharp
Super User
Hi.polingjw
The code can change somewhat.


Sorry.I am wrong.


Ksharp Message was edited by: Ksharp
GVeers
Calcite | Level 5
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.
GVeers
Calcite | Level 5
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;
ArtC
Rhodochrosite | Level 12
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1283 views
  • 0 likes
  • 6 in conversation