topic Re: Proc SQL in SAS Studio

Proc SQL

Shakhane — Thu, 25 Jul 2019 08:22:57 GMT

Could you please direct me where the error originated or how to resolve the error!

Thanks

Shakhane

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 proc sql;

74 create table work.weston_milling_sas

75 as

76 select intake.name, intake.xlsx.sap code, warehouse.xlsx.sap code

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,

AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH,

LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

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

77 from weston_milling_sas.intake.xlsx intake.xlsx

78 inner join westin_milling_sas.warehouse.xlsx warehouse.xlsx

79 from unknown table string null unknown table string

80 on;

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

81 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

83 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: Proc SQL

PeterClemmensen — Thu, 25 Jul 2019 08:26:17 GMT

I think the log is pretty clear?

However, I don't think you want a three level name in your select Clause. Also, you use the alias 'code' for two variables.

Re: Proc SQL

Shakhane — Thu, 25 Jul 2019 08:44:40 GMT

Could you please highlight the alias 'code' for me, may I am not sure what are you talking about!

Re: Proc SQL

Kurt_Bremser — Thu, 25 Jul 2019 08:52:20 GMT

ALWAYS use the {i} button to post logs. ALWAYS.

Your log, correctly posted:

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
72        
73         proc sql;
74         create table work.weston_milling_sas
75         as
76         select intake.name, intake.xlsx.sap code, warehouse.xlsx.sap code
                                           _
                                           22
                                           76

You can see that the pointer points to the second dot in your column name, and that is not valid SQL syntax.

You also have invalid dataset references and aliases containing more than one dot:

from weston_milling_sas.intake.xlsx intake.xlsx

This is also invalid.

I strongly recommend you study the SQL Procedure User's Guide to get a grasp of SQL basics.

Re: Proc SQL

Kurt_Bremser — Thu, 25 Jul 2019 08:56:36 GMT

PS you have so many catastrophic errors in your code that it is next to impossible to deduct what you are trying to do.

Please supply

the correct names of your datasets (library.dataset)
the names of the columns within the datasets (no dots allowed there)
the columns you want to use for the join condition
the columns you want to have in the output, and from which dataset