SAS read a table from a SQL Server connection with name containing special characters

1
votes

I have a connection in SAS to a sql server table where the table name is 'Additions_to_Aggregate$'. The quotes are part of the name. So in my SAS editor when I try to run the below in part of my code, I'm returned errors because SAS is reading it as string rather than as the table name. 

proc sql;
Create Table Name_Compare as
SELECT DISTINCT a.Insured_Name, agg.Policy_Holder_Name, a.Segment
FROM MySQLLib.ADV_Portfolio_Split as a
LEFT JOIN MySQLLib.'Additions_to_Aggregate$'n.data as agg 
on a.Insured_Name = agg.Policy_Holder_Name;
quit;

Is there any way to force SAS to read the table name as a literal string, or do you have any other solution ideas? I already tried renaming the table in SAS explorer but I get this error and don't know how to interpret it.

SAS Error Message

1
sqlsql-serversas
@BeanFrog Assuming this is SAS syntax, that doesn't work in SAS as far as I'm aware, that is SQL Server syntax. – Joe
Just to clarify, Cole, you're not doing this in pass-through (connect to sql... select * from connection to sql ( )) but in SAS syntax environment using LIBNAME connection (MySQL is a libname to a sql database)? – Joe
@Joe Yes this is SAS syntax. I edited my original post to show that. Also, letting you know I posted a reply to your original solution. – Cole

1 Answers

1
votes

You're looking for a name literal. Either:

LEFT JOIN MySQL.'Additions_to_Aggregate$'n

or

LEFT JOIN MySQL."'Additions_to_Aggregate$'"n

depending on how SAS handles the quotes in the DBMS connection; it may or may not require the second, outside pair of quotes. If for some reason you need single quotes around it (SAS doesn't have any special meaning for single/double outside of macro resolution), you can double them up:

LEFT JOIN MySQL.'''Additions_to_Aggregate$'''n