10-20-2015 10:24 AM
I am new with SAS and running below query in SAS editor and getting error
ERROR 22-322: Syntax error, expecting one of the following: ), ',', ANSIMISS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT,
JOIN, LEFT, NATURAL, NOMISS, OUTER, RIGHT, UNION, WHERE
I am running below query
libname mydblib oracle user=ABCD password=A12345 path=msrprod2 schema=MCA;
create table brand as
select PROD,IND_ID from (
select 'DR' as PROD,IND_ID AS IND_ID
select 'HF' as PROD,IND_ID AS IND_ID
select 'ROH' as PROD,IND_ID AS IND_ID
select 'NBB' as PROD,IND_ID AS IND_ID
select 'EMR' as PROD,IND_ID AS IND_ID
Please let me know what is the correct way
10-20-2015 10:59 AM - edited 10-20-2015 11:02 AM
I'm not an expert in SQL but, in my guess i would say to close with "()" in each "select" or use the except option between them.
10-20-2015 11:02 AM
Can't see any problem in your code. Are you sure that's the exact code you are submitting?
Please post the complete log including the submitted code part.
If you are not sure about the syntax, try the same with a sample SASHELP table, I got it to work.
10-21-2015 07:15 AM
I am still getting the same error. Please help
Below is the complete log
81 libname mydblib oracle user=ABCD password=A12345 path=msrprod2 schema=MCA;
NOTE: Libref MYDBLIB was successfully assigned as follows:
Physical Name: msrprod2
82 proc sql;
83 create table brand as
84 select PROD,IND_ID from (
85 select 'DR' as PROD,IND_ID AS IND_ID
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS,
CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
86 FROM UNIC.DR
ERROR 22-322: Syntax error, expecting one of the following: a name, (, ), ',', '.', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP,
HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, OUTER, RIGHT, UNION, WHERE.
ERROR 76-322: Syntax error, statement will be ignored.
88 select 'HF' PROD_ABBR,INDIVIDUAL_ID_ID
89 FROM UNICARUNUS.HF
91 select 'ROH' PROD_ABBR,INDIVIDUAL_ID_ID
92 FROM UNICARUNUS.ROH
94 select 'NBB' PROD_ABBR,INDIVIDUAL_ID_ID
95 FROM UNICARUNUS.NBB
97 select 'EMR' PROD_ABBR,INDIVIDUAL_ID_ID
98 FROM UNICARUNUS.EMR);
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
10-21-2015 07:53 AM
Thanks for posting the log. Would you mind trying a couple of things please. This should just take a minute.
1) Can you try running the individual select statements within the subquery as stand-alone to ensure they run fine.
2) Combine 2 queries with a union; if it works include the rest with union as well.
3) Try to enclose each select query within parantheses that are joined by the Union.
4) Include all queries within a high level parantheses and then bring in the wrapper query where you select the data from the result of the union.
This should hopefully point out the exact location of the issue. Please post the log if you can, Thanks and good luck my friend...!!!
10-20-2015 11:01 PM