topic Re: SAS Macro errors in SAS Programming

SAS Macro errors

nazmul — Thu, 09 Nov 2023 01:28:52 GMT

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

Re: SAS Macro errors

Patrick — Thu, 09 Nov 2023 02:01:58 GMT

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,

Re: SAS Macro errors

nazmul — Thu, 09 Nov 2023 01:54:54 GMT

Hi Patrick:

Thank you for your response.

SAS codes:

%macro do_over(values, between, phrase);
%mend;

proc sql;
create table Kothari_1 as
select a.*, b.intercept + %do_over(values=inv_at_l drevadj ppe roa,
between=%str(+), phrase=a.? * b.?) 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:
18037 %macro do_over(values, between, phrase);
18038 %mend;
18039
18040 proc sql;
18041 create table Kothari_1 as
18042 select a.*, b.intercept + %do_over(values=inv_at_l drevadj ppe roa,
between=%str(+),
18042! 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, ^=, |, ||, ~=.

18043 a.tac - calculated fitted as DA_Kothari_w_minm10,
18044 abs (calculated DA_Kothari_w_minm10) as ABSDA_Kothari_w_minm10
18045 from main_sample_w a left join kothari_w b
18046 on a.sic2 = b.sic2 and a.fyear = b.fyear
18047 and b._EDF_ > 5 ;
18048 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

Re: SAS Macro errors

nazmul — Thu, 09 Nov 2023 02:04:01 GMT

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

Re: SAS Macro errors

Patrick — Thu, 09 Nov 2023 02:21:59 GMT

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);

Re: SAS Macro errors

ballardw — Thu, 09 Nov 2023 03:12:21 GMT

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.

Re: SAS Macro errors

PaigeMiller — Thu, 09 Nov 2023 13:05:16 GMT

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

Re: SAS Macro errors

Quentin — Thu, 09 Nov 2023 15:10:11 GMT

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.

Re: SAS Macro errors

PaigeMiller — Thu, 09 Nov 2023 15:16:04 GMT

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.