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
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
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
I agree with BallardW, that certainly is a problem. The MACRO processor sure makes that code hard to read.
PG
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.
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
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
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.
Many thanks. I think it fixed the error. The pitfalls of copying and pasting and not thinking.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.