Cole Cole - 1 month ago 10
SQL Question

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

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

Joe Joe
Answer

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