DATA Step, Macro, Functions and more

proc sql in a %Do loop with math calculation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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.

 

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

 


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

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

Posted in reply to michellekwai

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;

 

View solution in original post


All Replies
Respected Advisor
Posts: 2,650

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

Posted in reply to michellekwai

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: 14

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

Posted in reply to PaigeMiller

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!

Respected Advisor
Posts: 2,650

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

Posted in reply to michellekwai

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

 

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

--
Paige Miller
Occasional Contributor
Posts: 14

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

Posted in reply to PaigeMiller

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

Respected Advisor
Posts: 2,650

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

Posted in reply to michellekwai

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: 14

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

Posted in reply to PaigeMiller

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,541

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

Posted in reply to michellekwai

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: 14

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

Posted in reply to Astounding

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

Occasional Contributor
Posts: 6

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

Posted in reply to michellekwai
Super User
Posts: 6,541

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

Posted in reply to michellekwai

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

 

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

 

 

Super Contributor
Posts: 472

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

Posted in reply to michellekwai

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: 14

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

Posted in reply to SuryaKiran

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

 

Best regards,

Michelle

Super Contributor
Posts: 472

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

Posted in reply to michellekwai

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: 14

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

Posted in reply to SuryaKiran

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 160 views
  • 8 likes
  • 5 in conversation