topic Re: Syntax error in explicit passthrough in SAS Programming

Syntax error in explicit passthrough

David_Billa — Tue, 27 Sep 2022 06:17:04 GMT

I'm getting the syntax error when I execute the following code in SAS EG. However it's working fine when I execute in SQL server. What would be the likely cause for the issue?

proc sql noprint;
  connect to sqlsvr(dsn=KNA_FINLAND
    user="SAS" 
    pwd="SAS002" 
    readbuff=32000);
     create table TEST as
    select * from connection to sqlsvr 
     

(
                SELECT MATRL_NBR
                                  ,CLAS
                                  ,CHRSTC_NM
                                  ,CHRSTC_VAL
                  FROM INPUT_TABLE
) as t1
                PIVOT (
                                max(CHRSTC_VAL)
                                FOR CHRSTC_NM in (IP_BU, IP_BRAND) 
) as t2;
disconnect from sqlsvr;

quit;

Log:

31               create table TEST as
32             select * from connection to sqlsvr
33              
34         
35          (
36                         SELECT MATRL_NBR
37                                           ,CLAS
38                                           ,CHRSTC_NM
39                                           ,CHRSTC_VAL
40                           FROM INPUT_TABLE
41         ) as t1
42                         PIVOT (
                           _____
                           22
                           76
ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING, ORDER, WHERE.  

ERROR 76-322: Syntax error, statement will be ignored.

43                                         max(CHRSTC_VAL)
44                                         FOR CHRSTC_NM in (IP_BU, IP_BRAND)
45         ) as t2;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
46         disconnect from sqlsvr;
NOTE: Statement not executed due to NOEXEC option.

Re: Syntax error in explicit passthrough

LinusH — Tue, 27 Sep 2022 07:19:42 GMT

It looks like a parenthesis matching problem.

Now the PIVOT instruction appears outside the block THAT IS sent to SQL Server.

How does the code look like when you execute it in SQL Server?

Re: Syntax error in explicit passthrough

David_Billa — Tue, 27 Sep 2022 07:22:44 GMT

@LinusH Following query works fine in SQL server

    select * from     
(
                SELECT MATRL_NBR
                                  ,CLAS
                                  ,CHRSTC_NM
                                  ,CHRSTC_VAL
                  FROM INPUT_TABLE
) as t1
                PIVOT (
                                max(CHRSTC_VAL)
                                FOR CHRSTC_NM in (IP_BU, IP_BRAND) 
) as t2;

Re: Syntax error in explicit passthrough

LinusH — Tue, 27 Sep 2022 07:35:33 GMT

Then I think it should look like this (untested):

proc sql noprint;
  connect to sqlsvr(dsn=KNA_FINLAND
    user="SAS" 
    pwd="SAS002" 
    readbuff=32000);
     create table TEST as
    select * from connection to sqlsvr 
     
(select * from     
(
                SELECT MATRL_NBR
                                  ,CLAS
                                  ,CHRSTC_NM
                                  ,CHRSTC_VAL
                  FROM INPUT_TABLE
) as t1
                PIVOT (
                                max(CHRSTC_VAL)
                                FOR CHRSTC_NM in (IP_BU, IP_BRAND) 
) as t2
);
quit