I have a large data set, and I need to create 2x2 tables to do some further calculation.
Part of he dataset looks like this:
data X_data
X_target1_result Y_target1_result result
Negative Positive
Negative Positive
Positive Negative
... ......
Where the "result" column is empty, since I have multiply targets to compare, so I need to generate a Macro to do it.
The macro code is:
*******************************************************************************
%MACRO compare(data, target, comp);
data data1 (replace=yes);
set &data;
%if "&comp._&target._result"="Positive" %then %do;
if "Y_&target._result"="Positive" then "result"=TP;
else if "Y_&target._result"="Negative" then "result"=FN;
%end;
%else %if "&comp._&target._result"="Negative" %then %do;
if "Y_&target._result"="Positive" then "result"=FP;
else if "Y_&target._result"="Negative" then "result"=TN;
%end;
run;
*********more proc sql and other code **********
%MEND;
**********************************************************************************
So When I run this Macro in the main program:
%include "¯opath.compare.sas";
%compare(X_data, target1, X)
This ends up give me the column "result" in data1 still empty, not fill with TP, TN, etc.
What mistake did I make and how to solve it?
Better would be to provide some example data and the calculations you need to do from those 2x2 tables. For example Proc Freq will do risks, risk differences, odds ratios and relative risks for 2x2 tables without much work.
You don't want to use %if / %then/ %do inside a data step when manipulative values of data step variables. Also if you want to use a variables value in a data step do not place the macro code for the name inside quotes.
if &comp._&target._result="Positive" then do; if Y_&target._result="Positive" then "result"=TP; else if Y_&target._result="Negative" then "result"=FN; end;
Second might be to consider coding text values lf "Positive" and "Negative" as 1 and 0. Use a custom format to assign desired display text as needed.
Use the following option before you run your macro.
options mprint;
Then run it again and show us the SASLOG. No, wait, you try to figure out what the error is from the SASLOG, and if you can't figure it out, then show us. Please click on the {i} icon and paste the RELEVANT parts of your SASLOG into the window that appears.
Better would be to provide some example data and the calculations you need to do from those 2x2 tables. For example Proc Freq will do risks, risk differences, odds ratios and relative risks for 2x2 tables without much work.
You don't want to use %if / %then/ %do inside a data step when manipulative values of data step variables. Also if you want to use a variables value in a data step do not place the macro code for the name inside quotes.
if &comp._&target._result="Positive" then do; if Y_&target._result="Positive" then "result"=TP; else if Y_&target._result="Negative" then "result"=FN; end;
Second might be to consider coding text values lf "Positive" and "Negative" as 1 and 0. Use a custom format to assign desired display text as needed.
First this warning .... you should not be using macro language. Not yet anyway. You need to understand base SAS first or macro language will be useless. Taking your program as an example, there is no SAS statement that looks like this:
"result" = FN;
If you want to refer to a variable named RESULT you would use:
result = FN;
Same thing here:
"Y_&target._result" =
should be
Y_&target._result =
In terms of getting the macro to work, you have done well to get rid of some of the percent signs. Now get rid of the rest.
if (not %if)
do (not %do)
end (not %end)
Since the macro conditions can never be true, no code is created, and the step does essentially NOTHING except copying the dataset.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.