topic Re: add a column which refers to a macro in SAS Programming

add a column which refers to a macro

axescot78 — Tue, 26 Jun 2018 09:05:55 GMT

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!

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

Community_Guide — Tue, 26 Jun 2018 09:08:46 GMT

Hello @axescot78,

Your question requires more details before experts can help. Can you revise your question to include more information?

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 @Kurt_Bremser

Re: add a column which refers to a macro

RW9 — Tue, 26 Jun 2018 09:24:31 GMT

"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.

Re: add a column which refers to a macro

axescot78 — Tue, 26 Jun 2018 10:31:33 GMT

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;

Re: add a column which refers to a macro

RW9 — Tue, 26 Jun 2018 10:46:17 GMT

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.

As for your provided information:

   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?

It should perhaps read:

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

Re: add a column which refers to a macro

Astounding — Tue, 26 Jun 2018 10:51:26 GMT

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;

Re: add a column which refers to a macro

Kurt_Bremser — Tue, 26 Jun 2018 11:03:04 GMT

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.

Re: add a column which refers to a macro

axescot78 — Tue, 26 Jun 2018 11:03:48 GMT

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;

Re: add a column which refers to a macro

axescot78 — Tue, 26 Jun 2018 11:12:31 GMT

Maybe I'm a Super User and didn't even know it! hahahahahaha

Re: add a column which refers to a macro

Kurt_Bremser — Tue, 26 Jun 2018 12:16:55 GMT

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;

Re: add a column which refers to a macro

axescot78 — Tue, 26 Jun 2018 17:07:23 GMT

I'm doing this for a class and part of the requirements is to use a macro 😉

Re: add a column which refers to a macro

axescot78 — Tue, 26 Jun 2018 17:09:20 GMT

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!

Re: add a column which refers to a macro

Kurt_Bremser — Tue, 26 Jun 2018 17:28:05 GMT

@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.

Re: add a column which refers to a macro

Kurt_Bremser — Wed, 27 Jun 2018 06:37:37 GMT

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