topic Re: Syntax Error in SAS Programming

Syntax Error

saspert — Tue, 16 Jul 2013 17:39:34 GMT

Hi,

Can anyone take a look at my proc sql syntax error. I am not able to spot the error.

SYMBOLGEN: Macro variable PCODE2 resolves to AE
MPRINT(_BUILD_METRICS): CREATE TABLE WORK.AE_LINE_DELTA_Voc AS SELECT
t1.AELine as t1.AELineA, t1._AE_CustomHrBin as _AE_CustomHrBinA, t1.AEUnit as AEUnitA,
t1._TYPE_, t1._FREQ_, t1.AE_Voc_Mean as AE_Voc_Mean_A , t2.AE_Voc_Mean AS AE_Voc_Mean_B ,
(t1.AE_Voc_Mean - t2.AE_Voc_Mean) AS AE_Voc_Delta, t1.AE_Voc_stddev as
t1.AE_Voc_stddev_A, t1.AE_Voc_var as t1.AE_Voc_var_A, t1.AE_Voc_sum as t1.AE_Voc_sum_A,
t1.AE_Voc_N as t1.AE_Voc_N_A, t1.AE_Voc_nmiss as t1.AE_Voc_nmiss_A, t2.AELine AS AELineB,
t2._AE_CustomHrBin AS _AE_CustomHrBinB, t2.AEUnit AS AEUnitB, t2._TYPE_ AS _TYPE_1, t2._FREQ_ AS _FREQ_1,
t2.AE_Voc_stddev AS total_eff_StdDev_B, t2.AE_Voc_var AS total_eff_Var_B, t2.AE_Voc_sum AS total_eff_Sum_B,
t2.AE_Voc_N AS total_eff_N_B, t2.AE_Voc_nmiss AS total_eff_NMiss_B FROM WORK.AE_LINE_DELTA_Voc t1,
WORK.AE_LINE_DELTA_Voc t2 WHERE (t1.AEUnit = t2.AEUnit AND t1._AE_CustomHrBin = t2._AE_CustomHrBin)
AND (t1.AELine CONTAINS 'A' AND t2.AELine CONTAINS 'B');
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN,
LENGTH, TRANSCODE.

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

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MPRINT(_BUILD_METRICS):   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.00 seconds

Thanks,

saspert

Re: Syntax Error

ballardw — Tue, 16 Jul 2013 17:52:42 GMT

I believe the problem is comming from the places you have the input table name in the AS clause such as:

t1.AELine as t1.AELineA

try removing the t1. from these:

t1.AELine as AELineA

Re: Syntax Error

OS2Rules — Tue, 16 Jul 2013 17:53:44 GMT

Hi:

Not sure if it is required, but I think you need the 'as' in these statements...

FROM WORK.AE_LINE_DELTA_Voc as t1,

WORK.AE_LINE_DELTA_Voc as t2

Re: Syntax Error

Peter_C — Tue, 16 Jul 2013 17:59:27 GMT

SELECT t1.AELine as t1.AELineA

never having tried it I can only guess that it causes an error when an alias appears after AS like in the first column of the SELECT

Re: Syntax Error

saspert — Tue, 16 Jul 2013 18:01:11 GMT

Many thanks. I think it fixed the error. The pitfalls of copying and pasting and not thinking.

Re: Syntax Error

PGStats — Tue, 16 Jul 2013 18:10:07 GMT

I agree with BallardW, that certainly is a problem. The MACRO processor sure makes that code hard to read.

Re: Syntax Error

ballardw — Tue, 16 Jul 2013 21:29:47 GMT

Doen't even need to be an alias, the processor doesn't appear to like any table reference following AS for variable selection. At least not in the test I did using 9.2.

Re: Syntax Error

Scott_Mitchell — Wed, 17 Jul 2013 06:03:22 GMT

I agree with BallardW.

This works:

PROC SQL;

CREATE TABLE WORK.AE_LINE_DELTA_VOC AS

SELECT CL1.SEX AS SEX1,

CL2.SEX AS SEX2

FROM SASHELP.CLASS CL1,

SASHELP.CLASS CL2

;

QUIT;

This doesn't:

PROC SQL;

CREATE TABLE WORK.AE_LINE_DELTA_VOC AS

SELECT CL1.SEX AS CL1.SEX1,

CL2.SEX AS CL2.SEX2

FROM SASHELP.CLASS CL1,

SASHELP.CLASS CL2

;

QUIT;

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT,

FROM, INFORMAT, INTO, LABEL, LEN, LENGTH, TRANSCODE.

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