topic Re: Please Help in New SAS User

Please Help with SQL syntax error and advice on the correction

MagD — Tue, 10 Sep 2019 19:33:07 GMT

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.

Re: Please Help

ballardw — Tue, 10 Sep 2019 19:30:11 GMT

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.

Re: Please Help

r_behata — Tue, 10 Sep 2019 19:33:26 GMT

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

Re: Please Help

MagD — Tue, 10 Sep 2019 19:42:19 GMT

It works!! Thank you so much!!