BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
saspert
Pyrite | Level 9

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









1 ACCEPTED SOLUTION

Accepted Solutions
OS2Rules
Obsidian | Level 7

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

7 REPLIES 7
ballardw
Super User

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

PGStats
Opal | Level 21

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

PG

PG
Scott_Mitchell
Quartz | Level 8

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.

OS2Rules
Obsidian | Level 7

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

Peter_C
Rhodochrosite | Level 12

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

ballardw
Super User

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.

saspert
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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