BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michellekwai
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

14 REPLIES 14
PaigeMiller
Diamond | Level 26

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
michellekwai
Obsidian | Level 7

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
Diamond | Level 26

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

 

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

--
Paige Miller
michellekwai
Obsidian | Level 7

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
Diamond | Level 26

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
michellekwai
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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

Astounding
PROC Star

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

 

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

 

 

SuryaKiran
Meteorite | Level 14

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
michellekwai
Obsidian | Level 7

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

 

Best regards,

Michelle

SuryaKiran
Meteorite | Level 14

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
michellekwai
Obsidian | Level 7

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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