Syntax error in explicit passthrough

[David_Billa]

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.

[LinusH]

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?

[David_Billa]

@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;

[LinusH · Accepted solution]

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