## proc sql in a %Do loop with math calculation

Solved
Occasional Contributor
Posts: 19

# proc sql in a %Do loop with math calculation

Hi everybody,

I use EG. I want to calculate p hat from my exist dataset. I write the equation in proc sql. The code is below, please help my why I come across this log error???  Is my do loop wrong? Or how can I detect my do loop problem? Or any other ways to calculate p hat? I need to add new column p_hat in the existing table.

%let variable=A,B,C

%let var_estimate=-0.0284,-0.0856,0.1242

%macro score(create_table=, original_table=, intercept=);

proc sql noprint;

create table &create_table as

select *, (1/(1+exp(-1*&intercept+

(%do i=1 %to %sysfunc(countw("&variable.",","));

%do j=1 %to %sysfunc(countw("&var_estimate.",","));

%if i = j %then %do;

catt(%scan("&variable.",&i.,","))*catt(%scan("&var_estimate.",&j.,","))

%end;

%end;

%end;

))))as phat

from &original_table;

quit;

%mend;

%score(create_table=create_table1, original_table=training_newvar3,intercept=-1.9190);

LOG ERROR:

))))as phat from &orig_table;

_

22

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.

Accepted Solutions
Solution
‎03-15-2018 03:12 PM
Super User
Posts: 6,934

## Re: proc sql in a %Do loop with math calculation

I think your program has gotten a little too complex.  This code contains more than is needed (while failing to add the + signs):

(%do i=1 %to %sysfunc(countw("&variable.",","));

%do j=1 %to %sysfunc(countw("&var_estimate.",","));

%if i = j %then %do;

catt(%scan("&variable.",&i.,","))*catt(%scan("&var_estimate.",&j.,","))

%end;

%end;

Try this variation.  Remove the plus sign following &INTERCEPT, check whether four closing parens should be three closing parens, then use:

%do i=1 %to %sysfunc(countw("&variable.",","));

%do j=1 %to %sysfunc(countw("&var_estimate.",",");

%if &i = &j %then %do;

+ %scan(&variable.,&i.,",")*%scan(&var_estimate.,&j.,",")

%end;

%end;

All Replies
Posts: 3,277

## Re: proc sql in a %Do loop with math calculation

My guess is that you have too many or too few parentheses once the macro is resolved.

Place this statement at the top of your code,

``options mprint;``

then re-run your code and your SASLOG will be much more readable and most likely the mis-matched parenthesis will be easy to find.

--
Paige Miller
Occasional Contributor
Posts: 19

## Re: proc sql in a %Do loop with math calculation

Hi Paige,

Thank you so much for your reply! I add the code as you mentioned. The log error shows me this (shown below). I can't understand what caused the problem....

26 ))))as phat from &orig_table;

_

22

MPRINT(SCORE): create table sandbox.create_table1 as select *, (1/(1+exp(-1*-1.9190+ ( ))) )as phat from sandbox.training_newvar3;

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.

Thank you so much!

Posts: 3,277

## Re: proc sql in a %Do loop with math calculation

(1/(1+exp(-1*-1.9190+ ( ))) )

You can't have two parenthesis with nothing in between them.

--
Paige Miller
Occasional Contributor
Posts: 19

## Re: proc sql in a %Do loop with math calculation

I really appreciate your help, Paige. As you said, it seems like I write some wrong do loop between the parathesis... Any idea how to fix the do loop problem. I want to create a new p_hat column, just adding in existing table. I think my logic is right to generate the multiplication of variable*estimate. But why sas can not show my do loop??

Best regard,

Michelle

Posts: 3,277

## Re: proc sql in a %Do loop with math calculation

The easiest thign to do is to get code working without macros on just one iteration of the loop. Then, since that works, you know  the parentheses are correct, and it would be easy enough to turn it into a macro that loops. If you have that working code, show it to us and we ought to be able to figure out what the macro should do.

--
Paige Miller
Occasional Contributor
Posts: 19

## Re: proc sql in a %Do loop with math calculation

I think the problem is very obvious now. The log error show below, and as a very beginning sas user, I may ask a tiny question, Do you know how to add "+" sign bwtween every var*estimate multiplication, so I can get a summary of those many multiplication...

MPRINT(SCORE): proc sql noprint;

NOTE: Line generated by the invoked macro "SCORE".

26 catt(%scan("&variable.",&i.,","))*catt(%scan("&var_estimate.",&j.,","))

____

22

76

MPRINT(SCORE): create table sandbox.create_table1 as select *, (1/(1+exp(-1*-1.9190+ ( catt(DF5_ATM_C_DR_1P)*catt(-0.0284) catt(

DF6_TOTALSERV)*catt(-0.0856) catt(LAG1_NSF_C_1P)*catt(0.1242) catt(LAG1_TOTALACCTS)*catt(0.00516)

catt(LAG1_TOTALSERV)*catt(-0.1438) catt(LAG2_FEE_A_WV_1P)*catt(0.000109) catt(LAG2_TOTALSERV)*catt(-0.1703)

catt(LAG3_DBT_C_1P)*catt(0.00166) catt(LAG3_RATE)*catt(0.0272) catt(LAG4_FEE_A_WV_1P)*catt(0.000248)

catt(LAG7_TOTALSERV)*catt(-0.0883) ))))as phat from sandbox.training_newvar3;

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,

CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

Solution
‎03-15-2018 03:12 PM
Super User
Posts: 6,934

## Re: proc sql in a %Do loop with math calculation

I think your program has gotten a little too complex.  This code contains more than is needed (while failing to add the + signs):

(%do i=1 %to %sysfunc(countw("&variable.",","));

%do j=1 %to %sysfunc(countw("&var_estimate.",","));

%if i = j %then %do;

catt(%scan("&variable.",&i.,","))*catt(%scan("&var_estimate.",&j.,","))

%end;

%end;

Try this variation.  Remove the plus sign following &INTERCEPT, check whether four closing parens should be three closing parens, then use:

%do i=1 %to %sysfunc(countw("&variable.",","));

%do j=1 %to %sysfunc(countw("&var_estimate.",",");

%if &i = &j %then %do;

+ %scan(&variable.,&i.,",")*%scan(&var_estimate.,&j.,",")

%end;

%end;

Occasional Contributor
Posts: 19

## Re: proc sql in a %Do loop with math calculation

I really appreciate Astounding!!!! It works!!! Thank you so much! You are right. It is too complicated!

Occasional Contributor
Posts: 6

Super User
Posts: 6,934

## Re: proc sql in a %Do loop with math calculation

In macro language, i can never equal j.  They are different letters.  You might try:

%if &i = &j %then %do;

PROC Star
Posts: 631

## Re: proc sql in a %Do loop with math calculation

If you have var_estimates same as variables then you don't need i & J. Avoid using CATT function for numeric variables and your missing '*' multiplication symbol for do loop. try something like this.

``````%let variable=Age,Height,weight;
%let var_estimate=-0.0284,-0.0856,0.1242;

%macro score(create_table=, original_table=, intercept=);
proc sql noprint;
create table &create_table as
select *, (1/(1+exp(-1*&intercept+
(%do i=1 %to %sysfunc(countw("&variable.",","));
%scan("&variable.",&i.,",")*%scan("&var_estimate.",&i.,",")*
%end;
1
)))) as phat
from &original_table;
quit;
%mend;
%score(create_table=test, original_table=sashelp.class, intercept=-1.9190);``````

Thanks,
Suryakiran
Occasional Contributor
Posts: 19

## Re: proc sql in a %Do loop with math calculation

Thank you! you are right! The code you share is much more accurate and simple! Thank you SuryaKiran!

Best regards,

Michelle

PROC Star
Posts: 631

## Re: proc sql in a %Do loop with math calculation

Note that the code @Astounding shared is doing addition and the one I shared is doing multiplication. Change it as your requirement.

(1/(1+exp(-1*&intercept+
(%do i=1 %to %sysfunc(countw("&variable.",","));
%scan("&variable.",&i.,",")*%scan("&var_estimate.",&i.,",")
%end;
1

This resolves to 1/(1+exp(-1*-1.9190+ ( Age*-0.0284 * Height*-0.0856* weight*0.1242* 1 )))

Where as

(1/(1+exp(-1*&intercept+
(%do i=1 %to %sysfunc(countw("&variable.",","));
+ %scan("&variable.",&i.,",")*%scan("&var_estimate.",&i.,",")
%end;

This resolves to 1/(1+exp(-1*-1.9190+ (+Age*-0.0284+ Height*-0.0856+ weight*0.1242 )))

Thanks,
Suryakiran
Occasional Contributor
Posts: 19

## Re: proc sql in a %Do loop with math calculation

Yes. I understand what you said. Even though I need to solve the addition problem, but you really give me good idea about what I should do about same number of variables. It helps to simplify the code. Thank you again!

Best regards,

Michelle

☑ This topic is solved.