Syntax error in SAS PROC SQL, works in DB2

[itsash]

Hi All,

I have a below query , which is running correctly in DB2, but when i run the same through SAS i get below error. Please help .

541         as NC_2,SUM (  CASE WHEN A.R_1='N'    AND A.R_2='N'    AND A.R_4='Y' then 1   else 0
541      ! end ) as NC_4   FROM (  SELECT CASE    WHEN (LENGTH(TRIM(TRANSLATE(cast(ABC_CT  as char(4000)), '
                                                                                                                   __
                                                                                                                   22
                                                                                                                   202
541      !           ',
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND,
              BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||,
              ~, ~=. 

ERROR 202-322: The option or parameter is not recognized and will be ignored.

**************************************

select

B.TOTAL_CT,

,B.NC_1 as NC_1

FROM (

SELECT COUNT(1) AS TOTAL_CT,SUM (

CASE WHEN A.R_1='Y' then 1

else 0

end ) as NC_1 ,SUM (

CASE WHEN A.R_1='N'

AND A.R_2='Y' then 1

else 0

end ) as NC_2,SUM (

CASE WHEN A.R_1='N'

AND A.R_2='N'

AND A.R_4='Y' then 1

else 0

end ) as NC_4

FROM (

SELECT CASE

WHEN (LENGTH(TRIM(TRANSLATE(cast(ABC_CT  as char(4000)), '          ', '+-.0123456789 '))) > 0) THEN 'Y'

ELSE 'N'

END AS R_1,

CASE WHEN ( ABC_CT <  10000 AND ABC_CT > 700) THEN'Y'

ELSE 'N'

END AS R_2,

CASE WHEN (ABC_CT <  0 AND ABC_CT > 600) THEN'Y'

ELSE 'N'

END AS R_4

FROM SASM.TABLENAME

)A)B

;

disconnect from db2;

quit;

**********************************************

Please help

[SASKiwi]

Possibly two commas in a row?

select

B.TOTAL_CT,

,B.NC_1 as NC_1

Also supplying the full SQL procedure step would be useful.

[itsash]

it is typo.. here is the full proc sql ..

proc sql;

connect to db2 (database=%sysget(DB2DBDFT) ril=UR);

INSERT INTO WRK.NEW_CL_TEST_ASH

select

B.TOTAL_CT,

B.NC_1,

B.NC_2

FROM (

SELECT COUNT(1) AS TOTAL_CT,SUM (

CASE WHEN A.R_1='Y' then 1

else 0

end ) as NC_1 ,SUM (

CASE WHEN A.R_1='N'

AND A.R_2='Y' then 1

else 0

end ) as NC_2,SUM (

CASE WHEN A.R_1='N'

AND A.R_2='N'

AND A.R_4='Y' then 1

else 0

end ) as NC_4

FROM (

SELECT CASE

WHEN (LENGTH(TRIM(TRANSLATE(cast(ABC_CT  as char(4000)), '          ', '+-.0123456789 '))) > 0) THEN 'Y'

ELSE 'N'

END AS R_1,

CASE WHEN ( ABC_CT <  0 AND ABC_CT > 600) THEN'Y'

ELSE 'N'

END AS R_2,

CASE WHEN (ABC_CT <  0 AND ABC_CT > 600) THEN'Y'

ELSE 'N'

END AS R_4

FROM SASM.&&TAB_NM&i

)A)B

;

disconnect from db2;

quit;

[Reeza]

casting - ERROR 22-322: Syntax error, expecting one of the following: using CAST - Stack Overflow

[SASKiwi · Accepted solution]

If your complete query is supposed to run in DB2 then you need to wrap an EXECUTE statement around it like this:

proc sql;

connect to db2 (database=%sysget(DB2DBDFT) ril=UR);

execute(

INSERT INTO WRK.NEW_CL_TEST_ASH

select

B.TOTAL_CT,

B.NC_1,

B.NC_2

FROM (

SELECT COUNT(1) AS TOTAL_CT,SUM (

CASE WHEN A.R_1='Y' then 1

else 0

end ) as NC_1 ,SUM (

CASE WHEN A.R_1='N'

AND A.R_2='Y' then 1

else 0

end ) as NC_2,SUM (

CASE WHEN A.R_1='N'

AND A.R_2='N'

AND A.R_4='Y' then 1

else 0

end ) as NC_4

FROM (

SELECT CASE

WHEN (LENGTH(TRIM(TRANSLATE(cast(ABC_CT  as char(4000)), '          ', '+-.0123456789 '))) > 0) THEN 'Y'

ELSE 'N'

END AS R_1,

CASE WHEN ( ABC_CT <  0 AND ABC_CT > 600) THEN'Y'

ELSE 'N'

END AS R_2,

CASE WHEN (ABC_CT <  0 AND ABC_CT > 600) THEN'Y'

ELSE 'N'

END AS R_4

FROM SASM.&&TAB_NM&i

)A)B

)

;

disconnect from db2;

quit;