BookmarkSubscribeRSS Feed
CG1
Calcite | Level 5 CG1
Calcite | Level 5

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

6 REPLIES 6
DartRodrigo
Lapis Lazuli | Level 10

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

LinusH
Tourmaline | Level 20

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.

Data never sleeps
CG1
Calcite | Level 5 CG1
Calcite | Level 5

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

kannand
Lapis Lazuli | Level 10

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...!!!

Kannan Deivasigamani
PGStats
Opal | Level 21

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.

PG
kannand
Lapis Lazuli | Level 10
Another test you might try to do to verify is to run the individual subqueries individually, one at a time, and then try a union and then have the higher query to select the 2 columns. I'd try this way step by step, so you know where the issue is... As the previous respondents indicated, if you can post the entire log, it will help see better. Good luck anyway...!
Kannan Deivasigamani

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
  • 6 replies
  • 2035 views
  • 0 likes
  • 5 in conversation