## add a column which refers to a macro

Solved
Occasional Contributor
Posts: 6

# add a column which refers to a macro

Hello,

Beginning SAS student here. I created a table with 49 rows, columns are 2 means and 2 stds. I need to add a column which refers to a macro that calculates CohensD based on the rows of data. I can't for the life of me figure it out!

Accepted Solutions
Solution
3 weeks ago
Occasional Contributor
Posts: 6

## Re: add a column which refers to a macro

I figured this out. If anyone is interested, here is the correct code and output:

%macro CohensD(m_1,m_2,s_1,s_2);
%let x = (abs(&m_1-&m_2))/(sqrt((&s_1**2 + &s_2**2)/2));
&x;
%mend CohensD;

proc iml;

Year = {1936, 1946, 1951, 1963, 1975, 1997, 2006};
x = nrow(Year);

Year1 = repeat(Year,x,1);
Year2 = repeat(Year[1],x,1) // repeat(Year[2],x,1) // repeat(Year[3],x,1) // repeat(Year[4],x,1) // repeat(Year[5],x,1) // repeat(Year[6],x,1) // repeat(Year[7],x,1);

CaloriesPerRecipeMean = {2123.8, 2122.3, 2089.9, 2250.0, 2234.2, 2249.6, 3051.9};
Mean1 = repeat(CaloriesPerRecipeMean,x,1);
Mean2 = repeat(CaloriesPerRecipeMean[1],x,1) // repeat(CaloriesPerRecipeMean[2],x,1) // repeat(CaloriesPerRecipeMean[3],x,1) // repeat(CaloriesPerRecipeMean[4],x,1) // repeat(CaloriesPerRecipeMean[5],x,1) // repeat(CaloriesPerRecipeMean[6],x,1) // repeat(CaloriesPerRecipeMean[7],x,1);

CaloriesPerRecipeSD = {1050.0, 1002.3, 1009.6, 1078.6, 1089.2, 1094.8, 1496.2};
SD1 = repeat(CaloriesPerRecipeSD,x,1);
SD2 = repeat(CaloriesPerRecipeSD[1],x,1) // repeat(CaloriesPerRecipeSD[2],x,1) // repeat(CaloriesPerRecipeSD[3],x,1) // repeat(CaloriesPerRecipeSD[4],x,1) // repeat(CaloriesPerRecipeSD[5],x,1) // repeat(CaloriesPerRecipeSD[6],x,1) // repeat(CaloriesPerRecipeSD[7],x,1);

create CookingTooMuch var {Year1, Year2, Mean1, Mean2, SD1, SD2};
append;
close CookingTooMuch;

quit;
proc print data=CookingTooMuch;
run;

data CookingTooMuch2;
set CookingTooMuch;
CohensD=%CohensD(Mean1,Mean2,SD1,SD2);
run;

proc print data=CookingTooMuch2;
run;

All Replies
SAS Moderator
Posts: 61

## Re: add a column which refers to a macro [how to improve your question]

Hello @axescot78,

Review this checklist:

• Specify a meaningful subject line for your topic.  Avoid generic subjects like "need help," "SAS query," or "urgent."
• When appropriate, provide sample data in text or DATA step format.  See this article for one method you can use.
• If you're encountering an error in SAS, include the SAS log or a screenshot of the error condition. Use the Photos button to include the image in your message.
• It also helps to include an example (table or picture) of the result that you're trying to achieve.

To edit your original message, select the "blue gear" icon at the top of the message and select Edit Message.  From there you can adjust the title and add more details to the body of the message.  Or, simply reply to this message with any additional information you can supply.

SAS experts are eager to help -- help them by providing as much detail as you can.

This prewritten response was triggered for you by fellow SAS Support Communities member @KurtBremser

.
Super User
Posts: 9,599

## Re: add a column which refers to a macro

"I need to add a column which refers to a macro that calculates CohensD based on the rows of data." - first things first.

Base SAS - this is the programming language.  It is fully functional with all data types and can do anything you want.

Macro - this is a sub-component designed for creating text, which can be code.  It is not used as a replacement for Base SAS.

Now from this, how does a column of values relate to a macro?  They don't, data in datasets is controlled and manipulated by Base SAS.  So there is your starting point, writing Base SAS to do the process.  Once you have that fully down, then you can look at methods to generalise code and such like using macro once you fully grasp what is being generated.  Don't run before you can walk and all that.

As for specifics, post code, example test data in the form of a datastep, show what the output should look like, any information at all that helps us, who cannot see your computer, to see the problem.

Occasional Contributor
Posts: 6

## Re: add a column which refers to a macro

This is the first 8 rows of the table that I created. I need to add another column that calculates CohensD, which takes values from  this table: (absolute value of mean1 - mean2) divided by sqrt((SD1^2+SD2^2)/2). The calculation needs to be done in a macro and the table columns are the macro's parameters.

My code is below. The parts in bold are where I am stuck. It's throwing an error: ERROR: Undeclared array referenced: CookingTooMuch. Also, is there special notation to pass an array into a macro?

%macro CohensD(m_1,m_2,s_1,s_2);
%let x = (abs(&m_1-&m_2))/(sqrt((&s_1**2 + &s_2**2)/2)));
&x;
%mend CohensD;

proc iml;

Year = {1936, 1946, 1951, 1963, 1975, 1997, 2006};
x = nrow(Year);

Year1 = repeat(Year,x,1);
Year2 = repeat(Year[1],x,1) // repeat(Year[2],x,1) // repeat(Year[3],x,1) // repeat(Year[4],x,1) // repeat(Year[5],x,1) // repeat(Year[6],x,1) // repeat(Year[7],x,1);

CaloriesPerRecipeMean = {2123.8, 2122.3, 2089.9, 2250.0, 2234.2, 2249.6, 3051.9};
Mean1 = repeat(CaloriesPerRecipeMean,x,1);
Mean2 = repeat(CaloriesPerRecipeMean[1],x,1) // repeat(CaloriesPerRecipeMean[2],x,1) // repeat(CaloriesPerRecipeMean[3],x,1) // repeat(CaloriesPerRecipeMean[4],x,1) // repeat(CaloriesPerRecipeMean[5],x,1) // repeat(CaloriesPerRecipeMean[6],x,1) // repeat(CaloriesPerRecipeMean[7],x,1);

CaloriesPerRecipeSD = {1050.0, 1002.3, 1009.6, 1078.6, 1089.2, 1094.8, 1496.2};
SD1 = repeat(CaloriesPerRecipeSD,x,1);
SD2 = repeat(CaloriesPerRecipeSD[1],x,1) // repeat(CaloriesPerRecipeSD[2],x,1) // repeat(CaloriesPerRecipeSD[3],x,1) // repeat(CaloriesPerRecipeSD[4],x,1) // repeat(CaloriesPerRecipeSD[5],x,1) // repeat(CaloriesPerRecipeSD[6],x,1) // repeat(CaloriesPerRecipeSD[7],x,1);

create CookingTooMuch var {Year1, Year2, Mean1, Mean2, SD1, SD2};
append;
close CookingTooMuch;

quit;
proc print data=CookingTooMuch;
run;

data CookingTooMuch2;
set CookingTooMuch;
CohensD=%CohensD(CookingTooMuch[M1],CookingTooMuch[M2],CookingTooMuch[S1],CookingTooMuch[S2]);
run;

proc print data=CookingTooMuch2;
run;

Super User
Posts: 9,599

## Re: add a column which refers to a macro

See, you have gone and fallen for the old chestnut again:

"The calculation needs to be done in a macro and the table columns are the macro's parameters."

Start by writing Base SAS - it is the programming language:

`data CookingTooMuch2; set CookingTooMuch; CohensD=(abs(mean1-mean2))/(sqrt((sd1**2 + sd2**2)/2)));run;`

There is no reason for macro anywhere in this code.

`   CohensD=%CohensD(CookingTooMuch[M1],CookingTooMuch[M2],CookingTooMuch[S1],CookingTooMuch[S2]);`

What is CookingTooMuch[M1] supposed to mean?  It almost appears you want to refer to an array, but have not created any?

`   CohensD=%CohensD(mean1,mean2,sd1,sd2);`

Super User
Posts: 6,753

## Re: add a column which refers to a macro

Despite not being an IML user (so I'm assuming your formula and use in context are correct), this looks like the right way to go:

%macro CohensD(m_1,m_2,s_1,s_2);
(abs(&m_1-&m_2))/(sqrt((&s_1**2 + &s_2**2)/2)))
%mend CohensD;

The semicolon can be removed at the end of the formula, because you have one in the program after calling the macro.

When you test it, add this first to make debugging easier:

options mprint;

Solution
3 weeks ago
Occasional Contributor
Posts: 6

## Re: add a column which refers to a macro

I figured this out. If anyone is interested, here is the correct code and output:

%macro CohensD(m_1,m_2,s_1,s_2);
%let x = (abs(&m_1-&m_2))/(sqrt((&s_1**2 + &s_2**2)/2));
&x;
%mend CohensD;

proc iml;

Year = {1936, 1946, 1951, 1963, 1975, 1997, 2006};
x = nrow(Year);

Year1 = repeat(Year,x,1);
Year2 = repeat(Year[1],x,1) // repeat(Year[2],x,1) // repeat(Year[3],x,1) // repeat(Year[4],x,1) // repeat(Year[5],x,1) // repeat(Year[6],x,1) // repeat(Year[7],x,1);

CaloriesPerRecipeMean = {2123.8, 2122.3, 2089.9, 2250.0, 2234.2, 2249.6, 3051.9};
Mean1 = repeat(CaloriesPerRecipeMean,x,1);
Mean2 = repeat(CaloriesPerRecipeMean[1],x,1) // repeat(CaloriesPerRecipeMean[2],x,1) // repeat(CaloriesPerRecipeMean[3],x,1) // repeat(CaloriesPerRecipeMean[4],x,1) // repeat(CaloriesPerRecipeMean[5],x,1) // repeat(CaloriesPerRecipeMean[6],x,1) // repeat(CaloriesPerRecipeMean[7],x,1);

CaloriesPerRecipeSD = {1050.0, 1002.3, 1009.6, 1078.6, 1089.2, 1094.8, 1496.2};
SD1 = repeat(CaloriesPerRecipeSD,x,1);
SD2 = repeat(CaloriesPerRecipeSD[1],x,1) // repeat(CaloriesPerRecipeSD[2],x,1) // repeat(CaloriesPerRecipeSD[3],x,1) // repeat(CaloriesPerRecipeSD[4],x,1) // repeat(CaloriesPerRecipeSD[5],x,1) // repeat(CaloriesPerRecipeSD[6],x,1) // repeat(CaloriesPerRecipeSD[7],x,1);

create CookingTooMuch var {Year1, Year2, Mean1, Mean2, SD1, SD2};
append;
close CookingTooMuch;

quit;
proc print data=CookingTooMuch;
run;

data CookingTooMuch2;
set CookingTooMuch;
CohensD=%CohensD(Mean1,Mean2,SD1,SD2);
run;

proc print data=CookingTooMuch2;
run;

Super User
Posts: 10,211

## Re: add a column which refers to a macro

There's still no reason to use a macro at all. It just moves code away from where it is used, and makes your whole code less readable and less maintainable, for no apparent gain. Remove the macro defintion and put the code where it belongs in the last data step:

``````data CookingTooMuch2;
set CookingTooMuch;
CohensD=(abs(Mean1-Mean2))/(sqrt((SD1**2 + SD2**2)/2));
run;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 6

## Re: add a column which refers to a macro

I'm doing this for a class and part of the requirements is to use a macro
Occasional Contributor
Posts: 6

## Re: add a column which refers to a macro

Actually, if you have a more elegant solution to how to get the 2nd, 4th, and 6th columns to repeat like that, I'm all ears!
Super User
Posts: 10,211

## Re: add a column which refers to a macro

@axescot78 wrote:
Actually, if you have a more elegant solution to how to get the 2nd, 4th, and 6th columns to repeat like that, I'm all ears!

Repeating code is a valid argument for a macro, in which case I have a suggestion to make your macro simpler:

``````%macro CohensD(m_1,m_2,s_1,s_2);
abs(&m_1-&m_2))/(sqrt((&s_1**2 + &s_2**2)/2);
%mend CohensD;``````

although the gain here (in terms of code that needs typing) is still very limited.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,211

## Re: add a column which refers to a macro

And when you need to repeat a formula over several columns, consider to use arrays and a do loop.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,211

## Re: add a column which refers to a macro

There's a conflict here:

Beginning SAS student

refers to a macro

As a beginner, you should NOT think about macro programming AT ALL. Since all SAS issues can be solved without the use of a macro (albeit inefficiently in some cases), you absolutely need to learn to solve those issues with Base SAS language alone. Only when you have a sufficiently advanced grasp of what can and should be done with Base SAS and the data step, you can start optimizing your code by using macro.

The macro preprocessor is not a problem solver, but a helper in making code writing more efficient.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 6