topic Re: Query a table with name that has a dot notation in New SAS User

Query a table with name that has a dot notation

vfarmak — Wed, 26 Jan 2022 10:14:00 GMT

Dear Communities,

I need to execute a PROC SQL on a table that has a dot in its name (i.e. esign.state is the table name).

I have registered a library that preserve table names with their original names.

LIBNAME DUW ODBC  DATAsrc=DUW  SCHEMA=gbox;
proc sql;
	create table Test_Q1 as 
		select * from duw.esign.state;
quit;

However, Enterprise Guide gives me the following error:

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING,
INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.

ERROR 200-322: The symbol is not recognized and will be ignored.

Do you know any workaround so as to resolve the issue?

Thank you in advance,

Vasilios

Re: Query a table with name that has a dot notation

Kurt_Bremser — Wed, 26 Jan 2022 10:46:48 GMT

Try

options validmemname=extend;

proc sql;
create table Test_Q1 as 
select * from duw.'esign.state'n;
quit;

You may have to take care of upper/lowercase inside the quotes.

Re: Query a table with name that has a dot notation

LinusH — Wed, 26 Jan 2022 13:51:43 GMT

Are you sure that the table actually has a dot in its name? In that case the DBA should be fired. Usually when I see a threee level name it's in the form of databse.schema.table. If so, you library reference should be directed to the schema, and then you could use a two level reference in your SQL.

Re: Query a table with name that has a dot notation

Kurt_Bremser — Wed, 26 Jan 2022 13:57:13 GMT

@LinusH wrote:
In that case the DBA should be fired.

And not just "fired from", but "fired at".

Re: Query a table with name that has a dot notation

vfarmak — Mon, 31 Jan 2022 10:22:02 GMT

Thank you very much @Kurt_Bremser

with the solution you provided I found out that SAS has a limitation in terms of the number of characters that can host in a character column (32767).

Therefore, I need to find a way to manipulate the string and store in SAS.

Best Regards,

Vasilios

Re: Query a table with name that has a dot notation

Kurt_Bremser — Tue, 01 Feb 2022 13:04:09 GMT

If the table in the DBMS has VARCHAR/CHAR fields that are too large for SAS, first cast them to a shorter length in a DBMS view, which you then use as source from SAS.