topic Re: Using "Union all" with Manual Values in Proc SQL Returns Error in SAS Enterprise Guide

Using "Union all" with Manual Values in Proc SQL Returns Error

sun538 — Wed, 29 Jul 2020 21:38:43 GMT

Hi,

I ran the following code to do union a table with some manual values :

proc sql;
SELECT DISTINCT id, sub_id 
FROM mytable
union all select 'A','A'
union all select 'B','B'
union all select 'C','C' ;
quit;

However, I gets an error saying :

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, 
              AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, 
              LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

Could you please let me know how I should change the code to perform well, as this same code is easily run in SQL Server. Thanks

Re: Using "Union all" with Manual Values in Proc SQL Returns Error

ballardw — Wed, 29 Jul 2020 22:14:34 GMT

Create another data set that contains three rows of data and union that data set

Many (most) implementations of SQL have extensions that are not standard ANSI SQL. So just because some syntax works in one do not expect all of the "features" to work in another SQL. I suspect that the set operators Union and such are expected to work on Tables in the standard SQL and you have encountered yet another extension that SQL Server supports that other SQLs don't.

Re: Using "Union all" with Manual Values in Proc SQL Returns Error

Tom — Wed, 29 Jul 2020 22:36:12 GMT

You cannot just SELECT, you have to have something to select FROM.

Just make a little dataset.

data template;
  if 0 then set mytable(keep=id sub_id);
  input id sub_id;
cards;
A A
B B
C C
;

proc sql;
SELECT DISTINCT id, sub_id FROM mytable
union all select id, sub_id FROM template
;
quit;