topic Re: PROC SQL in SAS Data Science

PROC SQL

Jonison — Thu, 18 Jul 2019 17:07:48 GMT

Hello, I have a node to extract data from database, below is my code. The List contains a column from 1st database, and extract a data whose MAT value is in List.

PROC SQL;
SELECT MATERIAL into: List separated by ',' FROM EMWS3.FILTER_TRAIN;
CREATE TABLE RETRIEVE_LIST AS
SELECT T2.BAT_NUM,
T2.MAT_COD,
T2.UNRESTRICTED_QTY
FROM EMWS3.Ids_DATA AS T2
WHERE T2.MAT_COD IN &List;
SELECT * FROM RETRIEVE_LIST;
QUIT;

When running the code, the errors pop up:

7915 CREATE TABLE RETRIEVE_LIST AS
7916 SELECT T2.BAT_NUM,
7917 T2.MAT_COD,
7918 T2.UNRESTRICTED_QTY
7919 FROM EMWS3.Ids_DATA AS T2
7920 WHERE T2.MAT_COD IN &List;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "LIST".
7920 63000000000014,63000000000015,63000000000013,10000000127482,10000000127483,10000000127484,10000000030330,10000000039898,10000000042667,10000000062024,10000000078514,10000000081175,10010000000076,10010000000109,61000000000254,61000000000596,R10
6
______________
______

22 76
ERROR 22-322: Syntax error, expecting one of the following: (, SELECT.

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

7920 ! 63000000000014,63000000000015,63000000000013,10000000127482,10000000127483,10000000127484,10000000030330,10000000039898,10000000042667,10000000062024,10000000078514,10000000081175,10010000000076,10010000000109,61000000000254,61000000000596,R10
6

______

22
7920 ! 63,R11274,
ERROR 22-322: Syntax error, expecting one of the following: a numeric constant, a datetime constant, a missing value, ), -.
7921 SELECT * FROM RETRIEVE_LIST;
NOTE: Statement not executed due to NOEXEC option.
7922 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: The PROCEDURE SQL printed page 13.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.14 seconds
cpu time 0.14 seconds

7922 !
7923
7924 /*; *"; *'; */;

Would you please advice on this?

Re: PROC SQL

novinosrin — Thu, 18 Jul 2019 17:12:31 GMT

/*Assuming your material variable is of type char, the following may work*/
/*quote each value of material before you load them into a macro var list*/

PROC SQL;
SELECT quote(MATERIAL) into: List separated by ',' FROM EMWS3.FILTER_TRAIN;
CREATE TABLE RETRIEVE_LIST AS
SELECT T2.BAT_NUM,
T2.MAT_COD,
T2.UNRESTRICTED_QTY
FROM EMWS3.Ids_DATA AS T2
WHERE T2.MAT_COD IN (&List);/*notice the parenthesis*/
SELECT * FROM RETRIEVE_LIST;
QUIT;

Re: PROC SQL

PaigeMiller — Thu, 18 Jul 2019 17:15:43 GMT

When you use macro variables like &LIST in SAS code, they are replaced by the value of the macro variable when the code executes, and this MUST result in valid legal working SAS code.

So, your code

WHERE T2.MAT_COD IN &List;

when you execute this, the value of macro variable &LIST is used and this results in code

where t2.mat_cod in 63000000000014,63000000000015,63000000000013,...

and this is not valid legal working SAS code — do you see the error now? What is missing that would make this valid legal working SAS code? (hint, the ERROR message tells you specifically what SAS is expecting)

Re: PROC SQL

Jonison — Fri, 19 Jul 2019 11:05:26 GMT

Many thanks for suggestions