DATA Step, Macro, Functions and more

Union issue

Reply
Occasional Contributor CG1
Occasional Contributor
Posts: 13

Union issue

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

Regular Contributor
Posts: 212

Re: Union issue

[ Edited ]

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

Super User
Posts: 5,424

Re: Union issue

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
Occasional Contributor CG1
Occasional Contributor
Posts: 13

Re: Union issue

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

Regular Contributor
Posts: 161

Re: Union issue

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
Respected Advisor
Posts: 4,919

Re: Union issue

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
Regular Contributor
Posts: 161

Re: Union issue

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
Ask a Question
Discussion stats
  • 6 replies
  • 244 views
  • 0 likes
  • 5 in conversation