17974 %macro do_over(values, between, phrase);
17975 %mend;
17976
17977 proc sql;
17978 create table Kothari_1 as
17979 /* fitted value computed as sum of coefficients in b multiplied by values in a */
17980 select a.*, b.intercept + %do_over(values=inv_at_l drevadj ppe roa, between=%str(+),
17980! phrase=a.? * b.?) as fitted,
------
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, '.',
/, <, <=, <>, =, >, >=, ?, AND, AS, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE,
LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
17981 /* abnormal accruals are ta - fitted */
17982 a.tac - calculated fitted as DA_Kothari_w_minm10,
17983 /* absolute abnormal accruals */
17984 abs (calculated DA_Kothari_w_minm10) as ABSDA_Kothari_w_minm10
17985 from main_sample_w a left join kothari_w b
17986 on a.sic2 = b.sic2 and a.fyear = b.fyear
17987 /* at a minimum 10 obs (5 degrees of freedom) */
17988 and b._EDF_ > 5 ;
17989 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds
Just what is this supposed to actually do (in words)
%macro do_over(values, between, phrase); %let sql_expr=%do_over(values=inv_at_l drevadj ppe roa, between=%str(+),phrase=a.? * b.?); %put %nrbquote(&sql_expr); %mend;
You do not use the parameters values, between or phrase anywhere, if you were the code should show &values &between and &phrase in the code.
The macro defines itself using itself, meaning it calls itself recursively and has no rule for stopping the recursion so likely over flows the macro space somewhere.
You haven't shared the macro definition with us nor have you shared SAS log with option mprint set.
This function style macro will pre-process. The SQL parser will look at what the macro returns.
After the macro executed it needs to return a string that's a SQL expression (or single value) that's valid SQL in the context where you're calling the macro.
From the looks of it the macro returns a string that's not valid SQL in the context where you use it. Without the actual macro code and a SAS log using option mprint that shows us to what the macro resolves it's not really possible to provide more guidance.
What you could do to ease debugging and maintenance:
1. First resolve the macro and store the result in a macro variable
%let sql_expr=%do_over(values=inv_at_l drevadj ppe roa, between=%str(+),phrase=a.? * b.?);
%put sql_expr: %nrbquote(&sql_expr);
2. Use the macro variable instead of the macro call in the SQL
b.intercept + &sql_expr as fitted,
This strikes me as a case of the XY Problem, where a programmer is trying to do something and achieve some end goal, and picks a particularly poor approach. Then the programmer gets stuck and asks for help in implementing this particularly poor approach. It also seems to me that the programmer @nazmul should take a step back, and describe the entire problem here, starting with a description of the data and continuing on to describe the end goal. (The end goal is not to get this macro code to work). At that point, I get the very strong feeling there are simpler methods available that may not even involve complicated macro coding.
One reason I get the feeling that simpler methods are available is the attempt to force DO loops and arithmetical formulas into PROC SQL. While you can do arithmetic in SQL, its usually not the best choice unless the arithmetic is extremely simple; there are usually better choices in SAS that involve less programming and also ought to execute faster.
So, @nazmul please describe the entire problem (which is not: how to get the macro to work).
Hi Patrick, I modified the code based on your suggestion and got the following errors.
SAS codes:
%macro do_over(values, between, phrase);
%let sql_expr=%do_over(values=inv_at_l drevadj ppe roa, between=%str(+),phrase=a.? * b.?);
%put %nrbquote(&sql_expr);
%mend;
proc sql;
create table Kothari_1 as
select a.*,
b.intercept + &sql_expr as fitted, a.tac - calculated fitted as DA_Kothari_w_minm10,
abs (calculated DA_Kothari_w_minm10) as ABSDA_Kothari_w_minm10
from main_sample_w a left join kothari_w b
on a.sic2 = b.sic2 and a.fyear = b.fyear
and b._EDF_ > 5 ;
quit;
Log file:
18063 %macro do_over(values, between, phrase);
18064 %let sql_expr=%do_over(values=inv_at_l drevadj ppe roa, between=%str(+),phrase=a.? * b.?);
18065 %put %nrbquote(&sql_expr);
18066 %mend;
18067
18068 proc sql;
18069 create table Kothari_1 as
18070 select a.*,
18071 b.intercept + &sql_expr as fitted, a.tac - calculated fitted as DA_Kothari_w_minm10,
-
22
WARNING: Apparent symbolic reference SQL_EXPR not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT,
SUBSTRING, USER.
18072 abs (calculated DA_Kothari_w_minm10) as ABSDA_Kothari_w_minm10
18073 from main_sample_w a left join kothari_w b
18074 on a.sic2 = b.sic2 and a.fyear = b.fyear
18075 and b._EDF_ > 5 ;
18076 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 second
What we need from you is the actual macro DEFINITION (the actual code between %macro and %mend).
Please run below code and share the SAS log with us. NOTE: You need to replace %macro do_over ... %mend with your actual code. You can run the rest of below "as is".
/* macro DEFINITION - here the macro gets Compiled */
%macro do_over(values=, between=, phrase=);
/* HERE SOME CODE!! */
%mend;
options mprint mlogic merror;
/* macro CALL: Here the macro gets Executed and the result gets assigned to macro variable &sql_expr */
%let sql_expr=%do_over(values=inv_at_l drevadj ppe roa, between=%str(+),phrase=a.? * b.?);
/* Write result of macro call to SAS log */
%put %nrbquote(&sql_expr);
Just what is this supposed to actually do (in words)
%macro do_over(values, between, phrase); %let sql_expr=%do_over(values=inv_at_l drevadj ppe roa, between=%str(+),phrase=a.? * b.?); %put %nrbquote(&sql_expr); %mend;
You do not use the parameters values, between or phrase anywhere, if you were the code should show &values &between and &phrase in the code.
The macro defines itself using itself, meaning it calls itself recursively and has no rule for stopping the recursion so likely over flows the macro space somewhere.
Hi,
The purpose of the macro language is to save you from typing code. In this case, use of a %do_over helper macro is a complex programming method. But you probably don't need it, you can just type the code yourself. Do you know the SQL code you want to have? It looks to me like, if it was working, this code:
select a.*, b.intercept
+ %do_over(values=inv_at_l drevadj ppe roa, between=%str(+), phrase=a.? * b.?) as fitted,
would be the same as typing this code (without the do_over macro):
select a.*, b.intercept
+ a.inv_at_l * b.inv_at_l + a.drevadj * b.drevadj + a.ppe * b.ppe + a.roa*b.roa as fitted,
So in this case, using the macro does not save you much typing. I would just type the code.
Good points, @Quentin .
And if the real problem that @nazmul is trying to solve has (for example) 40 columns that need to be added, then this goes back to my point that SQL is not a good tool for arithmetic in general, unless it is extremely simple math. If this is the case, a DATA step would make a lot more sense than doing this in SQL.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.