Syntax error

[Sandeep77]

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;

[Reeza]

At minimum the last select is missing a closing parenthesis, but doubt that's the full issue. Have you tested the inner query works before that distinct query?

[Sandeep77]

Hi, but the last select does have a parenthesis
left join (select *from connection to oledb
(select * from [dbLetters].[dbo].[tReturnedMailDailyReturns]))

[PaigeMiller]

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.*

[Sandeep77]

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;

[PaigeMiller]

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

[Reeza]

The parenthesis is too early on the CASE statement.

[Sandeep77]

The value of macro variable is
%let PC_variable = tcfPostCode ;
TCFpostcode is basically the post code.

[PaigeMiller]

---

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

[Kurt_Bremser]

The log should show the contents of the macro variable if it was resolved successfully, not its name.

[Tom]

A few obvious but mainly cosmetic things to check/fix.

• It is impossible to read when the lines are so long. Avoid code lines longer than 70-80 bytes.
• Do you really want the DISTINCT keyword?  Why?
• Do you really want to use A.* with GROUP BY ?  SAS will have to REMERGE the aggregate values onto the detail observations.
• The "AS" keyword before the table references feels wrong.  SAS might accept it (and some strange SQL dialects might require it) but it still feels wrong.
• Do not have a space between a function name (missing) and the () around the argument list.
• Place ending semicolon for a multiple line statement on a NEW line where it is visible to humans.

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
;