topic Re: Union issue in SAS Programming

Union issue

CG1 — Tue, 20 Oct 2015 14:24:56 GMT

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

Re: Union issue

DartRodrigo — Tue, 20 Oct 2015 15:02:53 GMT

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

Re: Union issue

LinusH — Tue, 20 Oct 2015 15:02:05 GMT

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.

Re: Union issue

PGStats — Tue, 20 Oct 2015 15:09:06 GMT

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.

Re: Union issue

kannand — Wed, 21 Oct 2015 03:01:50 GMT

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

Re: Union issue

CG1 — Wed, 21 Oct 2015 11:15:39 GMT

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

Re: Union issue

kannand — Wed, 21 Oct 2015 11:53:50 GMT

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