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

[sun538]

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

[ballardw]

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.

[Tom]

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;