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
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.