proc sql in a %Do loop with math calculation

[michellekwai]

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.

Please!!! Thank you so much for your help!!

[PaigeMiller]

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.

[michellekwai]

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!

[PaigeMiller]

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

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

[michellekwai]

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

[PaigeMiller]

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.

[michellekwai]

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.

[Astounding · Accepted solution]

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;

[michellekwai]

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

[Stephen]

This may help: 

How to determine logistic regression formula from estimates output

[Astounding]

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

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

[SuryaKiran]

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

[michellekwai]

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

Best regards,

Michelle

[SuryaKiran]

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

[michellekwai]

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