DATA Step, Macro, Functions and more

Syntax Error

Accepted Solution Solved
Reply
Super Contributor
Posts: 275
Accepted Solution

Syntax Error

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










Accepted Solutions
Solution
‎07-16-2013 01:53 PM
Super Contributor
Posts: 358

Re: Syntax Error

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

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Syntax Error

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

Respected Advisor
Posts: 4,919

Re: Syntax Error

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

PG

PG
Super Contributor
Posts: 297

Re: Syntax Error

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.

Solution
‎07-16-2013 01:53 PM
Super Contributor
Posts: 358

Re: Syntax Error

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

Valued Guide
Posts: 2,177

Re: Syntax Error

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

Super User
Posts: 11,343

Re: Syntax Error

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.

Super Contributor
Posts: 275

Re: Syntax Error

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1010 views
  • 4 likes
  • 6 in conversation