topic Syntax error in WHERE statement - proc sql in SAS Programming

Syntax error in WHERE statement - proc sql

CathyVI — Mon, 22 Aug 2022 07:00:32 GMT

Hi,

I would like to merge two data set and also create a new variable by using proc sql. This is my code but am getting the following syntax error.

proc sql;

create table final_table_0607 as

select * from case_07 as x left join mth_0607 as y

on x.bene_id = y.bene_id

where month_fu-(mondx1+12) as month_std;

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

CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT,

NOTIN, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=.

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

Re: proc sql

Kurt_Bremser — Sun, 21 Aug 2022 10:35:41 GMT

The WHERE clause needs a valid Boolean expression.

month_fu-(mondx1+12) as month_std

is not a Boolean expression, it rather looks like a part of the SELECT clause.

Re: proc sql

Sajid01 — Sun, 21 Aug 2022 12:27:22 GMT

Looks like you want to create a new variable month_std. I would write the code as follows

proc sql;
create table final_table_0607 as
select *,x.month_fu-(x.mondx1+12) as month_std
 from case_07 as x left join mth_0607 as y
on x.bene_id = y.bene_id;
quit;

I have taken x.month_fu and x.modx1 as an example assuming it comes from the first table. You can take what ever is applicable in your case (x or y).