Hi All. Please kindly assist.
Below code:
proc sql;
create table contract_file as
select
account_guid as 'CLIENT NUMBER'n,
case when current_edc = "Blake" then "18811"
when current_edc = "ARA" then "18810"
else "18453"
end as 'MERCHANT NUMBER'n label = 'MERCHANT NUMBER',
case when pp_opening_invoice_age = "0" as 'CONTRACT NUMBER'n
end as 'CONTRACT NUMBER'n,
min(current_balance, installment) as 'AMOUNT'n,
'1' as 'NUMBER OF INSTALLMENTS'n,
'ABSANAEDO' as 'PRODUCT CODE'n,
'O' as 'FREQUENCY'n,
dhms(next_debit_order_date,0,0,0) as 'DATE'n format dtpic.,
'03' as 'TRACKING CODE'n,
'' as 'ED / EOM'n
from BASE_file;
quit;
I get the below error:
34 case when pp_opening_invoice_age = "0" as 'CONTRACT NUMBER'n
__
22
202
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 202-322: The option or parameter is not recognized and will be ignored.
The Error is in the below statement :
case when pp_opening_invoice_age = "0" as 'CONTRACT NUMBER'n
replace it with something like :
case
when pp_opening_invoice_age = "0" then "xxxxxx"
end
as 'CONTRACT NUMBER'n,
Log with noexec option without errors :
26 proc sql noexec; 27 create table contract_file as 28 select 29 account_guid as 'CLIENT NUMBER'n, 30 case 31 when current_edc = "Blake" then "18811" 32 when current_edc = "ARA" then "18810" 33 else "18453" 34 end 35 as 'MERCHANT NUMBER'n label = 'MERCHANT NUMBER', 36 case 37 when pp_opening_invoice_age = "0" then "xxxxxx" 38 end 39 as 'CONTRACT NUMBER'n, 40 min(current_balance, installment) as 'AMOUNT'n, 41 '1' as 'NUMBER OF INSTALLMENTS'n, 42 'ABSANAEDO' as 'PRODUCT CODE'n, 43 'O' as 'FREQUENCY'n, 44 dhms(next_debit_order_date,0,0,0) as 'DATE'n format dtpic., 45 '03' as 'TRACKING CODE'n, 46 '' as 'ED / EOM'n 47 from BASE_file; NOTE: Statement not executed due to NOEXEC option. 48 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 79.62k OS Memory 18084.00k
Please paste error messages along with the entire proc code into a code box opened using the forum's {I} icon.
The main windows on the forum will reformat text. Which moves the underscore from indicating where SAS detected the error it is reporting.
Likely the issue is having two
as 'CONTRACT NUMBER'n
bits. Only the one after the End of the case should be there.
The Error is in the below statement :
case when pp_opening_invoice_age = "0" as 'CONTRACT NUMBER'n
replace it with something like :
case
when pp_opening_invoice_age = "0" then "xxxxxx"
end
as 'CONTRACT NUMBER'n,
Log with noexec option without errors :
26 proc sql noexec; 27 create table contract_file as 28 select 29 account_guid as 'CLIENT NUMBER'n, 30 case 31 when current_edc = "Blake" then "18811" 32 when current_edc = "ARA" then "18810" 33 else "18453" 34 end 35 as 'MERCHANT NUMBER'n label = 'MERCHANT NUMBER', 36 case 37 when pp_opening_invoice_age = "0" then "xxxxxx" 38 end 39 as 'CONTRACT NUMBER'n, 40 min(current_balance, installment) as 'AMOUNT'n, 41 '1' as 'NUMBER OF INSTALLMENTS'n, 42 'ABSANAEDO' as 'PRODUCT CODE'n, 43 'O' as 'FREQUENCY'n, 44 dhms(next_debit_order_date,0,0,0) as 'DATE'n format dtpic., 45 '03' as 'TRACKING CODE'n, 46 '' as 'ED / EOM'n 47 from BASE_file; NOTE: Statement not executed due to NOEXEC option. 48 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 79.62k OS Memory 18084.00k
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.