topic Re: proc sql in a %Do loop with math calculation in SAS Programming

proc sql in a %Do loop with math calculation

michellekwai — Thu, 15 Mar 2018 18:06:53 GMT

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;

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

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

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

PaigeMiller — Thu, 15 Mar 2018 18:15:40 GMT

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.

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

Stephen — Thu, 15 Mar 2018 18:20:02 GMT

This may help:

How to determine logistic regression formula from estimates output

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

michellekwai — Thu, 15 Mar 2018 18:21:11 GMT

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;

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!

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

PaigeMiller — Thu, 15 Mar 2018 18:30:14 GMT

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

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

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

Astounding — Thu, 15 Mar 2018 18:31:34 GMT

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

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

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

michellekwai — Thu, 15 Mar 2018 18:35:21 GMT

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

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

PaigeMiller — Thu, 15 Mar 2018 18:40:54 GMT

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.

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

michellekwai — Thu, 15 Mar 2018 18:47:09 GMT

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

____

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.

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

Astounding — Thu, 15 Mar 2018 19:09:05 GMT

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;

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;

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

michellekwai — Thu, 15 Mar 2018 19:13:52 GMT

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

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

SuryaKiran — Thu, 15 Mar 2018 19:22:48 GMT

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

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

michellekwai — Thu, 15 Mar 2018 19:27:34 GMT

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

Best regards,

Michelle

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

SuryaKiran — Thu, 15 Mar 2018 19:40:40 GMT

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

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

michellekwai — Thu, 15 Mar 2018 19:50:53 GMT

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