Sintax error using insert from a Teradata table

jorquec — Thu, 13 Feb 2020 10:34:27 GMT

Hi all,

Could some on please help me with this bit of code? I do not have an idea how to fix it .

Many thanks in advance

proc SQL;
INSERT INTO jorquec.sp_handset_base2    

SELECT a.*,                
       MIN(device_type_id) OVER (PARTITION BY subscr_id ORDER BY effective_from_dt ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)  AS prev_device,
           CASE WHEN device_type_id = prev_device THEN 0 ELSE 1 END AS flag
                
FROM PROFTMDL.sp_handset_base a
WHERE device_type_id <> -1 ;

QUIT;

______________________________________

LOG FILE :

23         GOPTIONS ACCESSIBLE;
24         


25         proc SQL;
26         INSERT INTO jorquec.sp_handset_base2
27         
28         SELECT a.*,
29                MIN(device_type_id) OVER (PARTITION BY subscr_id ORDER BY effective_from_dt ROWS BETWEEN 1 PRECEDING AND 1
                                      ____
                                      22
                                      76
29       ! PRECEDING)  AS prev_device,
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.

30                    CASE WHEN device_type_id = prev_device THEN 0 ELSE 1 END AS flag
31         
32         FROM PROFTMDL.sp_handset_base a
33         WHERE device_type_id <> -1 ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
34         
35         QUIT;
NOTE: The SAS System stopped processing this step because of errors.

Re: Sintax error using insert from a Teradata table

Kurt_Bremser — Thu, 13 Feb 2020 11:41:14 GMT

In proc sql, you have to use SAS SQL syntax. "over" is not a valid syntax element in SAS SQL.

You will have to build a sub-select.

If you post usable example data (data step with datalines) and what you expect out of it, we can make up code for this.

topic Re: Sintax error using insert from a Teradata table in New SAS User

Sintax error using insert from a Teradata table

Re: Sintax error using insert from a Teradata table