topic Re: Calculations in array with macro variables in SAS Programming

Calculations in array with macro variables

alexgonzalez — Mon, 20 Apr 2020 16:31:13 GMT

Hi there,

I’m trying to do some calculation using previously specified macro variables (i.e. v1, v2, and v3) within a macro. I’ve the following code, but it’s not working.

%let v1 = 10;

%let v2 = 15;

%let v1 = 13;

data z2;

set z;

array diff {3} diff1-diff3;

array vars {3} a b c;

%do i = 1 %to 3;

diff{i} = nt{i}-&&v&i

%end;

run;

I have tried many different options but nothing is working. Any recommendation please?

Thank you very much.

A.G.

Re: Calculations in array with macro variables

Reeza — Mon, 20 Apr 2020 16:38:54 GMT

%let v1 = 10;

%let v2 = 15;

%let v1 = 13; <- should be v3?

What does not working mean? Did you get any errors or warnings in the log?

Set the debugging options, run your code and post the log if you have issues after you fix the issue noted above.

options mprint symbolgen;

*your code;

@alexgonzalez wrote:

Hi there,

I’m trying to do some calculation using previously specified macro variables (i.e. v1, v2, and v3) within a macro. I’ve the following code, but it’s not working.

%let v1 = 10;

%let v2 = 15;

%let v1 = 13;

data z2;

set z;



array diff {3} diff1-diff3;

array vars {3} a b c;

       %do i = 1 %to 3;

           diff{i} = nt{i}-&&v&i

     %end;

run;

I have tried many different options but nothing is working. Any recommendation please?

Thank you very much.

A.G.

Re: Calculations in array with macro variables

Reeza — Mon, 20 Apr 2020 16:42:34 GMT

And I would recommend simplifying your code to remove most of the macros and use a temporary array.

%let v1 = 10;
%let v2 = 15;
%let v3 = 13;

data z2;
set z;           
array diff {3} diff1-diff3;
array vars {3} a b c;
array _v(3) _temporary_ (&v1 &v2 &v3);

       do i = 1 to 3;
            diff{i} = nt{i} -  _v(i)
       end;     
 run;

Re: Calculations in array with macro variables

Jagadishkatam — Mon, 20 Apr 2020 16:43:20 GMT

please try

%let v1 = 10;
%let v2 = 15;
%let v3 = 13;

data z;
input Var$ value;
cards;
a 20
b 17
c 30
;

proc transpose data=z out=z1;
id var;
var value;
run;


%macro test;
data z2;
set z1;
  array diff{3} diff1-diff3;
  array vars{3} a b c;
 %do i = 1 %to 3;
            diff(&i) = vars(&i)-&&v&i;
       %end;     
run;
%mend;

%test;

Re: Calculations in array with macro variables

ballardw — Mon, 20 Apr 2020 17:12:11 GMT

macro instructions are applied at compilation of the data step, so if you expect a loop such as DO to reference VARIABLES in the data set you do no want want %do, %end.

Re: Calculations in array with macro variables

alexgonzalez — Mon, 20 Apr 2020 17:28:13 GMT

Hi Reeza,

This works perfectly, but since I do not know for sure the number of variables that will be in the temporary array you suggest, I'm not sure then how to make it work. See below:

array _v {&number_nutr.} _temporary_ (&v1 - &&v&number_nutr);

%do i = 1 %to &number_nutr.;
diff{i} = nt{i}-_v(i);
%end;

As you can see in the previous code, I'm passing in the array &number_nutr variables. When using the - sign to specify all the variables, then it gives an error (WARNING: Partial value initialization of the array _v.). Any further suggestion would be very much appreciated.

Re: Calculations in array with macro variables

Tom — Mon, 20 Apr 2020 17:33:16 GMT

Why do you have so many macro variables? If you want to use those values together put them into ONE macro varaible.

Re: Calculations in array with macro variables

Tom — Mon, 20 Apr 2020 17:41:11 GMT

Aren't you the same user that asked how to get the values out of data and into macro variables?

https://communities.sas.com/t5/SAS-Programming/Assigning-macro-variables-to-input-parameters/m-p/641333

Why not just leave the values in the dataset to begin with.

Looks like you haven't explained your real problem, just the problems you are having with the design you have chosen. http://xyproblem.info/

Please explain the larger issue. You probably just want to combine the original datasets together and then you can do the subtraction.

Re: Calculations in array with macro variables

alexgonzalez — Mon, 20 Apr 2020 17:54:29 GMT

Hi Tom,

You're right, that was me. I just wanted to ask what I needed at that time. For that that simplified my question. I got the answer I needed for both questions, but when I tried to code the second one in the macro, then I got some issues. Outside the macro it worked perfectly. I'll try to explain quickly to you the problem. I've a macro to perform some analyses. I pass some variables as a unique parameter. For example:

%macro example (vars);

data t;

input mylib.mydata (keep = &vars);

run;

%mend;

%example (vars= a b c);

At some point, I need to calculate the difference between some variables and those in the parameter (i.e. &vars) in a data set. Since I don't always pass the same number of variables, I assign to macro variables in the code that value (i.e. &number_nutr). Then I want to do the following:

array nt {&number_nutr.} &nutrients;
array diff {&number_nutr.} diff1-diff&number_nutr;
array v {&number_nutr.} _temporary_ (&value1 - &&value&number_nutr);

%do i = 1 %to &number_nutr.;
diff{i} = nt{i}- v(i);
%end;

The - sign in the 'V' array between and &&value&number_nutr is not appropiate to use. I hope this is more clear.

Thanks a lot,

AG.

Re: Calculations in array with macro variables

Tom — Mon, 20 Apr 2020 18:04:30 GMT

You are still skipping some large step in the middle of your explanation. You say you start with a list of VARIABLE names. Then some how you start talking about MACRO VARIABLES. When did you make the jump from variables to macro variables? Why did you make the jump? Are they supposed to be the same in some sense? If so how are the two related.

If you have a list of VARIABLE names in the macro variable NURIENTS. And a second list of WHAT? (offset values?) in a second dataset that is using the variables listed in the macro variable VARS then it looks like you want to write this code:

data want;
  set have;
  if _n_=1 then set offsets(keep=&vars);
  array A &nutrients;
  array B &vars;
  array diff [%sysfunc(countw(&nutrients,%str( )))];
  do i=1 to dim(a);
   diff[i]=a[i]-b[i];
  end;
  drop i &vars;
run;

The only thing that needs to know the number of variables is the definition of the size of the array that is creating the NEW variables. But you can just count how many you have. (Plus the two macro variables have to have the same number of variables listed.)

Re: Calculations in array with macro variables

Reeza — Mon, 20 Apr 2020 18:18:46 GMT

@alexgonzalez How are you creating this macro variables instead? I suspect changing that would be easier.

proc sql noprint;
select distinct age into :age_list separated by " "
from sashelp.class;
quit;
%let nCount = &sqlObs;

%put &age;
%put &ncount;

And arrays can be wild carded. I don't think that works for temporary arrays but the count should help you above. It would simplify your logic a lot.

array myArray (*) myVar1-myVar&nCount;

Re: Calculations in array with macro variables

Tom — Mon, 20 Apr 2020 18:26:11 GMT

For permanent arrays you don't need to specify the number of elements. But for temporary arrays you MUST.

Use the nCount variable your code creates.

array age_list [&nCount] _temporary_  (&age_list) ;

Re: Calculations in array with macro variables

alexgonzalez — Tue, 21 Apr 2020 15:38:11 GMT

Hi Tom,

First of all, thanks a lot for taking the time for this, very much appreciated. I’ll provide you with all the information you need. Just want to mention that I simplified the macro for you, and change variable names and datasets for simplification purpose. So it is probably (not very, I hope so) that if you run this macro you could probably get some error.

Let’s say I’ve one object with multiple characteristics (variables), and I want to compare it with other objects on the same characteristics using the Euclidean distance.

%macro example (vars);

data one;

input mylib.one (keep = id &vars); /*One record only*/

run;

data many;

input mylib.many (keep = id &vars); /*Multiple records*/

run;

*Counting the # of specified variables in the ‘vars’ parameter and creating a var list;

proc transpose data=one out=z (drop=_label_ );

var &var;

run;

proc sql noprint;

select distinct _name_ into :var_list separated by " "

from z;

quit;

%let number_vars = &sqlObs;

*Assigning macro values to each variable in the &vars parameter;

data _null_ ;

set z ;

call symputx(cats("value",_n_),col1) ;

run;

*Preparing dataset the calculation of the Euclidean distance;

data all;

set one many;

run;

*Calculating the Euclidean distance;

proc distance data=all out=Dist1 (keep= dist1) method=Euclid;

var interval(&vars);

run;

data dist2;

set dist1;

if _n_=1 then delete;

rename dist1 = d;

run;

*Matching each object with a distance value;

data many_dist;

merge many dist2;

*Renaming &vars before merging back with ‘One’;

%do i = 1 %to &number_vars.;

%scan(&vars.,&i.) = key_&i.;

%end;

run;

*Getting the final dataset;

*This dataset will contain in the first records and 1+& number_vars columns,

the information from ‘One’. The subsequent columns (and as many records

as in the dataset ‘many’ the information from many_dist.;

data final

merge on many_dist;

*Here is where I want to calculate the difference between

&value1-key_1, &value2-key_2 … &valueK-key_K using arrays.

As you can notice the values &value1 to &valueK are the same that appear

in the second to +& number_vars columns, but only in the first row.;

run;

%mend;

%example (vars= a b c); *The # of variables in the ‘vars’ parameter can change;

I hope this is clear enough. Looking forward to getting your inputs.

Thanks again,

AG.

Re: Calculations in array with macro variables

ballardw — Tue, 21 Apr 2020 21:57:19 GMT

I would suggest providing a small example of a starting data set and what the result should be.

Since you have not provided an actual example data set we can't test your code (if this is supposed to work) to see what a result should be.

I get a feeling that there is a lot of complication involved.

And maybe belongs in Proc IML. Maybe.

Re: Calculations in array with macro variables

alexgonzalez — Wed, 22 Apr 2020 16:00:15 GMT

That's a great suggestion Ballardw. I should have though about it, appologie. See below everything you need. The code runs perfectly.

data a; 
input id $ a b c d e;
datalines; 
001 100 70 85 86 94 
;

data b; 
input id $ a b c;
datalines; 
002 87 78 60 
003 99 45 77
004 103 95 73 
;


%macro example (vars);

data one;
set a (keep = id &vars); /*One record only*/
run;

data many;
set b (keep = id &vars); /*Multiple records*/
run;

*Counting the # of specified variables in the ‘vars’ parameter and creating a var list;

proc transpose data=one out=z;
var &vars;
run;

proc sql noprint;
select distinct _name_ into :var_list separated by " "
from z;
quit;
%let number_vars = &sqlObs;

*Assigning macro values to each variable in the &vars parameter;

data _null_ ;
set z ;
call symputx(cats("value",_n_),col1) ;
run;

*Preparing dataset the calculation of the Euclidean distance;

data all;
set one many;
run;

*Calculating the Euclidean distance;

proc distance data=all out=Dist (keep= dist1) method=Euclid;
var interval(&vars);
run;

data dist;
set dist;
if _n_=1 then delete;
rename dist1 = d;
run;

*Matching each object with a distance value;

data many_dist;
merge many dist;

*Renaming &vars before merging back with ‘One’;
rename id = idcode;

%do i = 1 %to &number_vars.;

rename %scan(&vars.,&i.) = key_&i.;

%end; 
run;

*Getting the final dataset;

*This dataset will contain in the first records and 1+& number_vars columns,
the information from ‘One’. The subsequent columns (and as many records
as in the dataset ‘many’ the information from many_dist.;

data final;
merge one many_dist;

/*Here is where I want to calculate the difference between
&value1-key_1, &value2-key_2 … &valueK-key_K using arrays.

As you can notice the values &value1 to &valueK are the same that appear
in the second to +& number_vars columns, but only in the first row.;*/
run;

proc datasets lib=work;
delete one many: all z dist;
run;

%mend;

%example (vars= a b c)

In the dataset 'final' I want to calculate three differences (maybe more depending on the # of variables in the &vars parameter), key_1-a, key_2-b, and key_3-c for each object 002,003, and 004. I hope this time I totally clear.

Thank you very much. A.G.

Re: Calculations in array with macro variables

Reeza — Wed, 22 Apr 2020 15:59:41 GMT

Post your code using the code button. It makes it easier to read and follow and prevents issues when copying and pasting your code from HTML to an editor.

Re: Calculations in array with macro variables

Tom — Wed, 22 Apr 2020 16:51:03 GMT

What is it that you want out of that? Why are you doing all of that crazy macro stuff?

https://forum.wordreference.com/threads/idiom-for-doing-something-simple-in-a-lengthier-way.3366230/

%let vars=a b c;

data all;
  set a b;
  keep &vars;
run;

proc distance data=all out=Dist(keep=Dist1 rename=Dist1=d) method=Euclid;
  var interval(&vars);
run;

data want;
  set b;
  set dist(firstobs=2) ;
run;

Obs     a      b     c       d

 1      87    78    60    29.2916
 2      99    45    77    26.2679
 3     103    95    73    27.8927

Re: Calculations in array with macro variables

Reeza — Wed, 22 Apr 2020 16:52:38 GMT

I definitely don't quite understand what you're trying to do overall, not sure why SET doesn't work as well? I feel like this could simplify your problem. However, assuming you want to stick with the approach identified, see a simplified solution below.

This would give the same solution as below - no macro logic besides the names and number of them needed which is trivial to get.

data final;
if _n_ =1 then set one;
set many_dist;


array diff(&number_vars) diff1-diff3;
array key(&number_vars) key:;
array _refs(&number_vars) &vars;

do i=1 to &number_vars.;
diff(i) = key(i) - _refs(i);
end;


run;

Some notes:

Your &vars and var_list seem to be the same macro variable so not sure why you need to recreate it.
Creating the macro variables can be simplified massively, which I use here within the same data step as 'One'
Rather than create multiple macro variables, I create a single one to use with all values in it.

data a;
	input id $ a b c d e;
	datalines;
001 100 70 85 86 94 
;

data b;
	input id $ a b c;
	datalines;
002 87 78 60 
003 99 45 77
004 103 95 73 
;

	%macro example (vars);
		%let number_vars = %sysfunc(countw(&vars)) ;

	data one;
		set a (keep=id &vars);

		/*One record only*/
		array _myvars(&number_vars) &vars.;
		call symputx('value_list', catx(' ', of _myvars(*)));
	run;

	data many;
		set b (keep=id &vars);

		/*Multiple records*/
	run;

	*Preparing dataset the calculation of the Euclidean distance;

	data all;
		set one many;
	run;

	*Calculating the Euclidean distance;

	proc distance data=all out=Dist (keep=dist1) method=Euclid;
		var interval(&vars);
	run;

	data dist;
		set dist;

		if _n_=1 then
			delete;
		rename dist1=d;
	run;

	*Matching each object with a distance value;

	data many_dist;
		merge many dist;
		*Renaming &vars before merging back with ‘One’;
		rename id=idcode;

		%do i=1 %to &number_vars.;
			rename %scan(&vars., &i.)=key_&i.;
		%end;
	run;

	data final;
		merge one many_dist;
		array diff(&number_vars) diff1-diff3;
		array key(&number_vars) key:;
		array _refs(&number_vars) _temporary_ (&value_list);

		do i=1 to &number_vars.;
			diff(i)=key(i) - _refs(i);
		end;
	run;

	data final2;
		if _n_=1 then
			set one;
		set many_dist;
		array diff(&number_vars) diff1-diff3;
		array key(&number_vars) key:;
		array _refs(&number_vars) &vars;

		do i=1 to &number_vars.;
			diff(i)=key(i) - _refs(i);
		end;
	run;

	proc datasets lib=work;
		delete one many: all z dist;
		run;
	%mend;

	%example (vars=a b c)

Re: Calculations in array with macro variables

Reeza — Wed, 22 Apr 2020 16:58:03 GMT

	%macro example (vars);
		%let number_vars = %sysfunc(countw(&vars)) ;



	*Preparing dataset the calculation of the Euclidean distance;

	data all;
		set a (keep=id &vars)  b (keep=id &vars);
	run;

	*Calculating the Euclidean distance;

	proc distance data=all out=Dist (keep=dist1) method=Euclid;
		var interval(&vars);
	run;

	data dist;
		set dist;

		if _n_=1 then
			delete;
		rename dist1=d;
	run;

	*Matching each object with a distance value;

	data many_dist;
		merge b (keep = id &vars) dist;
		*Renaming &vars before merging back with ‘One’;
		rename id=idcode;

		%do i=1 %to &number_vars.;
			rename %scan(&vars., &i.)=key_&i.;
		%end;
	run;



	data final;
		if _n_=1 then
			set a (keep = id &vars);
			
		set many_dist;
		array diff(*) diff1-diff&number_vars;
		array key(*) key:;
		array _refs(*) &vars;

		do i=1 to dim(diff);
			diff(i)=key(i) - _refs(i);
		end;
	run;

	proc datasets lib=work;
		delete  many: all  dist;
		run;
	%mend;

	%example (vars=a b c)

This is the full simplified version that matches the results.

Re: Calculations in array with macro variables

alexgonzalez — Wed, 22 Apr 2020 18:52:27 GMT

That worked Reeza. Thanks a lot for your great suggestions. I modified my macro it's much better now. I tried the code in the 'final' data set (with the other suggestions in the code). Very much appreciated.