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;
... View more
