Hi,
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;
proc sql;
create table brand as
select PROD,IND_ID from (
select 'DR' as PROD,IND_ID AS IND_ID
FROM UNIC.DR
UNION
select 'HF' as PROD,IND_ID AS IND_ID
FROM UNIC.HF
UNION
select 'ROH' as PROD,IND_ID AS IND_ID
FROM UNIC.ROH
UNION
select 'NBB' as PROD,IND_ID AS IND_ID
FROM UNIC.NBB
UNION
select 'EMR' as PROD,IND_ID AS IND_ID
FROM UNIC.EMR);
quit;
Please let me know what is the correct way
Hi friend,
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.
See Proc SQL - Combining Queries with Set Operators
Att
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.
Hi,
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:
Engine: ORACLE
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
---------
22
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
----
22
76
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.
87 UNION
88 select 'HF' PROD_ABBR,INDIVIDUAL_ID_ID
89 FROM UNICARUNUS.HF
90 UNION
91 select 'ROH' PROD_ABBR,INDIVIDUAL_ID_ID
92 FROM UNICARUNUS.ROH
93 UNION
94 select 'NBB' PROD_ABBR,INDIVIDUAL_ID_ID
95 FROM UNICARUNUS.NBB
96 UNION
97 select 'EMR' PROD_ABBR,INDIVIDUAL_ID_ID
98 FROM UNICARUNUS.EMR);
99 quit;
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
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...!!!
I see nothing wrong with the syntax. Maybe something wrong happened before in the session. Try starting a new SAS session, if you can, and resubmit the query.
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.