Hi all,
I am running a code which I think is correct but I am getting syntax error and I am unable to spot what is wrong with the code. Can you please check the code and log and help me to spot the error please? Thanks
proc sql;
connect to oledb (provider=sqlncli11.1
properties = ("Integrated Security" = SSPI
"Persist Security Info" = False
"Initial Catalog" = dbLetters
prompt = NO
"Data Source" = 'ORPHEUS'
read_lock_type = no));
create table Returned_Mail as
select distinct
a.*
sum(Case when not missing (a.&PC_variable) and compress(upcase(b.Postcode)) = compress(upcase(a.&PC_variable)))
then 1 else 0
end as Returned_mail
from Complaints as a
left join (select *from connection to oledb
(select * from [dbLetters].[dbo].[tReturnedMailDailyReturns])) as b on a.AccountNumber =b.debtcode
group by a.AccountNumber;
quit;
Error log:
29 proc sql;
30 connect to oledb (provider=sqlncli11.1
31 properties = ("Integrated Security" = SSPI
32 "Persist Security Info" = False
33 "Initial Catalog" = dbLetters
34 prompt = XX
35 "Data Source" = 'ORPHEUS'
36 read_lock_type = no));
37 create table Returned_Mail as
38 select distinct
39 a.*
40 sum(Case when not missing (a.&PC_variable) and compress(upcase(b.Postcode)) = compress(upcase(a.&PC_variable)))
___
22
76
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.
41 then 1 else 0
42 end as Returned_mail
43 from Complaints as a
44 left join (select *from connection to oledb
2 The SAS System 14:02 Tuesday, January 31, 2023
45 (select * from [dbLetters].[dbo].[tReturnedMailDailyReturns])) as b on a.AccountNumber =b.debtcode
46 group by a.AccountNumber;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
47 quit;
Hi, but the last select does have a parenthesis
left join (select *from connection to oledb
(select * from [dbLetters].[dbo].[tReturnedMailDailyReturns]))
39 a.* 40 sum(Case when not missing (a.&PC_variable) and compress(upcase(b.Postcode)) = compress(upcase(a.&PC_variable)))
There should be a comma after a.*
I added the comma after a.* but still getting the error. Please see the updated code and error log below:
proc sql;
connect to oledb (provider=sqlncli11.1
properties = ("Integrated Security" = SSPI
"Persist Security Info" = False
"Initial Catalog" = dbLetters
prompt = NO
"Data Source" = 'ORPHEUS'
read_lock_type = no));
create table Returned_Mail as
select distinct
a.*,
sum(Case when not missing (a.&PC_variable) and compress(upcase(b.Postcode)) = compress(upcase(a.&PC_variable)))
then 1 else 0
end as Returned_mail
from Complaints as a
left join (select *from connection to oledb
(select * from [dbLetters].[dbo].[tReturnedMailDailyReturns])) as b on a.AccountNumber =b.debtcode
group by a.AccountNumber;
quit;
Error log:
29 proc sql;
30 connect to oledb (provider=sqlncli11.1
31 properties = ("Integrated Security" = SSPI
32 "Persist Security Info" = False
33 "Initial Catalog" = dbLetters
34 prompt = XX
35 "Data Source" = 'ORPHEUS'
36 read_lock_type = no));
37 create table Returned_Mail as
38 select distinct
39 a.*,
40 sum(Case when not missing (a.&PC_variable) and compress(upcase(b.Postcode)) = compress(upcase(a.&PC_variable)))
_
22
200
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET,
GT, GTT, LE, LET, LT, LTT, NE, NET, NOT, OR, THEN, ^, ^=, |, ||, ~, ~=.
ERROR 200-322: The symbol is not recognized and will be ignored.
41 then 1 else 0
42 end as Returned_mail
__
79
2 The SAS System 14:02 Tuesday, January 31, 2023
ERROR 79-322: Expecting a ).
43 from Complaints as a
44 left join (select *from connection to oledb
45 (select * from [dbLetters].[dbo].[tReturnedMailDailyReturns])) as b on a.AccountNumber =b.debtcode
______
79
76
ERROR 79-322: Expecting a ).
ERROR 76-322: Syntax error, statement will be ignored.
46 group by a.AccountNumber;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
47 quit;
It doesn't like the value of your macro variable &PC_variable. What is the value of this macro variable before you run PROC SQL?
40 sum(Case when not missing (a.&PC_variable) and compress(upcase(b.Postcode)) = compress(upcase(a.&PC_variable))) _ 22 200 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET, GT, GTT, LE, LET, LT, LTT, NE, NET, NOT, OR, THEN, ^, ^=, |, ||, ~, ~=. ERROR 200-322: The symbol is not recognized and will be ignored.
SAS is complaining about mismatched parenthesis earlier than your
left join (select *from connection to oledb (select * from [dbLetters].[dbo].[tReturnedMailDailyReturns]))
Here:
41 then 1 else 0 42 end as Returned_mail __ 79 ERROR 79-322: Expecting a ).
@Sandeep77 wrote:
The value of macro variable is
%let PC_variable = tcfPostCode ;
TCFpostcode is basically the post code.
In the context of this problem, it really doesn't matter what the variable means ... let me ask you this ... is tcfpostcode the name of a variable in data set Complaints?
Also, just before PROC SQL, run this command and report the results which should get written to the log
%put &=pc_variable;
The log should show the contents of the macro variable if it was resolved successfully, not its name.
A few obvious but mainly cosmetic things to check/fix.
But the real issue is probably your parentheses placements.
1) Add () around the WHEN condition.
2) CLOSE the () for the SUM() aggregate function.
Formatting will help with making it easier to check the () placements.
create table Returned_Mail as
select distinct
a.*
,sum(
case when (not missing(a.&PC_variable)
and compress(upcase(b.Postcode))=compress(upcase(a.&PC_variable))
)
then 1 else 0
end
) as Returned_mail
from Complaints a
left join
(select * from connection to oledb
(select * from [dbLetters].[dbo].[tReturnedMailDailyReturns])
) b
on a.AccountNumber=b.debtcode
group by a.AccountNumber
;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.