BookmarkSubscribeRSS Feed
Sandeep77
Lapis Lazuli | Level 10

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;
10 REPLIES 10
Reeza
Super User
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
Lapis Lazuli | Level 10

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

PaigeMiller
Diamond | Level 26
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.*

--
Paige Miller
Sandeep77
Lapis Lazuli | Level 10

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

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

 

 

 

--
Paige Miller
Reeza
Super User
The parenthesis is too early on the CASE statement.
Sandeep77
Lapis Lazuli | Level 10
The value of macro variable is
%let PC_variable = tcfPostCode ;
TCFpostcode is basically the post code.
PaigeMiller
Diamond | Level 26

@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;
--
Paige Miller
Tom
Super User Tom
Super User

 

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
;

SAS Innovate 2025: Call for Content

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!

Submit your idea!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 10 replies
  • 1073 views
  • 4 likes
  • 5 in conversation