topic Re: Getting syntax error, expecting one of the following: in SAS Programming

Getting syntax error, expecting one of the following:

Lakshmisukanya — Wed, 05 Apr 2023 17:26:33 GMT

Proc SQL;
Create table consumer as select * from ( select arng,src, src_sys as app,substring(srcprd,18) as sub,
Substring(entryprd,10) as keis
From cc inner join pp on cc.arng =pp.arng);
Quit;

Re: Getting syntax error, expecting one of the following:

PaigeMiller — Wed, 05 Apr 2023 17:30:04 GMT

Show us the ENTIRE log for this PROC SQL (that's every single line in the log for this PROC SQL, that's every single character in the log for this PROC SQL, with nothing removed).

Re: Getting syntax error, expecting one of the following:

Tom — Wed, 05 Apr 2023 19:35:06 GMT

What is SUBSTRING()?

Did you mean SUBSTR()?

Re: Getting syntax error, expecting one of the following:

Lakshmisukanya — Thu, 06 Apr 2023 02:40:44 GMT

The substr() function is used to extract a specific substring from a string. It takes in three parameters, the string to extract from, the starting position, and the length of the substring.

Example:

SELECT SUBSTR('hello world', 2, 4);

Output: "ello"

The substring() function is similar to substr(), but it takes in two parameters, the string to extract from, and the starting position. It extracts all the characters from the starting position until the end of the string.

Example:

SELECT SUBSTRING('hello world', 2);

Output: "ello world"

Re: Getting syntax error, expecting one of the following:

SASKiwi — Thu, 06 Apr 2023 02:52:32 GMT

SUBSTR and SUBSTRING are equivalent:

Note: The SUBSTRING function is provided for compatibility with the ANSI SQL standard. You can also use the SAS function SUBSTR.

Re: Getting syntax error, expecting one of the following:

Tom — Thu, 06 Apr 2023 03:10:54 GMT

Perhaps in theory SAS supports SUBSTRING() as an alias for SUBSTR(), but in reality it does not.

 68         
 69         %put &=sysvlong;
 SYSVLONG=9.04.01M7P080620
 70         proc sql;
 71         
 72         create table TEST as
 73         select name,substr(name,1,5) as sub
 74         from sashelp.class
 75         ;
 NOTE: Table WORK.TEST created, with 19 rows and 2 columns.
 
 76         create table TEST as
 77         select name,substring(name,1,8) as sub
                                      _     __
                                      79    22
                                            76
 ERROR 79-322: Expecting a (.
 
 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
               CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, 
               ~=.  
 
 ERROR 76-322: Syntax error, statement will be ignored.
 
 78         from sashelp.class
 79         ;
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 80         quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds

The syntax of SUBSTRING is some perverse oververbose sql-eze instead of a simple function call.

proc sql;
create table TEST1 as
select name
     , substring(name from 1 for 5) as sub1
     , substr(name,1,5) as sub2
from sashelp.class
;
quit;

Re: Getting syntax error, expecting one of the following:

Kurt_Bremser — Thu, 06 Apr 2023 04:50:54 GMT

Reads like COBOL 😉

Re: Getting syntax error, expecting one of the following:

PaigeMiller — Thu, 06 Apr 2023 09:43:29 GMT

@Lakshmisukanya wrote:

The substr() function is used to extract a specific substring from a string. It takes in three parameters, the string to extract from, the starting position, and the length of the substring.

SHOW US THE LOG