topic How to make refference to a table with 33 characters long in SAS Enterprise Guide

How to make refference to a table with 33 characters long

OliveiraMiguelZ — Tue, 31 Aug 2021 19:02:30 GMT

I am trying to use an Access (MS Access) table with a name larger than 32 characters long in a PROC SQL join statement but it look like impossible to do so.

Does anyone knows how deal with that?

I've tried link the table to another one but it still doesn't work for SAS doesn't recognise it as a table. Also tried to use CONNET TO DBMS ( ... ), wich is the answer I found in StackOverflow, but the problem stay still.

Remembering that I can't simply just rename the table because it's part of some proccesses that came from external parts that i don't have how to rename.

Re: How to make refference to a table with 33 characters long

Reeza — Tue, 31 Aug 2021 19:12:52 GMT

The connect to DBMS() option - aka direct pass through is the correct option. Please show what you've tried in that regards and we can help you modify your code as needed.

Re: How to make refference to a table with 33 characters long

OliveiraMiguelZ — Tue, 31 Aug 2021 20:09:05 GMT

I am not that good with this kind of connection, I tried something like this:

PROC SQL;
    CONNECT TO OLEDB ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\myfilepath.mdb;")
    SELECT * FROM CONNECTION TO OLEDB ( 
       SELECT * FROM MyRecordsetName
     );
QUIT;

Re: How to make refference to a table with 33 characters long

Tom — Tue, 31 Aug 2021 20:14:34 GMT

@OliveiraMiguelZ wrote:

I am not that good with this kind of connection, I tried something like this:
PROC SQL;
    CONNECT TO OLEDB ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\myfilepath.mdb;")
    SELECT * FROM CONNECTION TO OLEDB ( 
       SELECT * FROM MyRecordsetName
     );
QUIT; 

Looks good. What happened? Did you get an error message?

I think that dialect of SQL allows you to use square brackets around object names.

 SELECT * FROM [MyRecordsetName]

Re: How to make refference to a table with 33 characters long

Reeza — Tue, 31 Aug 2021 21:29:22 GMT

PROC SQL;
    CONNECT TO OLEDB ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\myfilepath.mdb;")
   create table smallerName as
 SELECT * FROM CONNECTION TO OLEDB ( 
       SELECT * FROM MyRecordsetName
     );
QUIT;

Other than the CREATE TABLE that's missing it seems correct to me. Did it not work for you?