DATA Step, Macro, Functions and more

ERROR 22-322: Syntax error, expecting one of the following:

Reply
New Contributor
Posts: 2

ERROR 22-322: Syntax error, expecting one of the following:

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

Super User
Posts: 3,115

Re: ERROR 22-322: Syntax error, expecting one of the following:

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.

New Contributor
Posts: 2

Re: ERROR 22-322: Syntax error, expecting one of the following:

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;

Super User
Posts: 17,912

Re: ERROR 22-322: Syntax error, expecting one of the following:

Super User
Posts: 3,115

Re: ERROR 22-322: Syntax error, expecting one of the following:

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;

Ask a Question
Discussion stats
  • 4 replies
  • 14774 views
  • 0 likes
  • 3 in conversation