SAS/ACCESS Interface to ODBC

[banaz]

Hello, 

I'm trying to connect to postgres sql database management system using SAS/Access Interface to ODBC. I've already installed the postgres ODBC driver. Below is the sql connect code I used to connect to the database. Then I used libname statement to import the tables from the database into SAS library. 

Here is the problem: When I look up the list of tables in the database using sql connect, there are 35 tables. However, when I use the libname statement to import the tables into SAS library- I am only able to import 30 tables into the SAS library. My question is- why can't I import the rest of the 5 tables in the database? Below is the code I used: 

proc sql; 

connect to odbc (user= user-name password= xx DSN= data-source-name);

create table work.odbcSchema as 

select * from connection to odbc

(ODBC:: SQLTables); quit; 

libname mylib odbc user=user-name password= xx noprompt="DSN= data-source-name" Schema= public;

[VDD]

Not knowing the policies of you servers, one reason is that those tables could be restricted to your access level, or they could be lookup tables containing meta data for the server.

you should ask your servers admin why you don't have access to them to confirm if you should have access to the tables.

[banaz]

@VDD. Thank you! I should be able to have access to all of them- don't think there is any restriction. I think it has to do with long table names that are >32 characters. See SASkiwi's post below.

[SASKiwi]

If any of the database table names are > 32 characters this would explain this behaviour. SAS is limited to a maximum of 32 characters when accessing tables in an external database via a LIBNAME. There is a workaround using SQL passthru but you have to know the table name to use this technique.

[banaz]

@SASKiwi yes that explains it! The tables that don't show up in the SAS library all have > 32 characters. I'm curious- how do I overcome this problem using SQL? I can see the names of the tables when I connect via SQL connect. For example, one of them is "wsibspecialty_camerge_billingoutcomes". Alternatively I could ask my team to shorten the name of these tables.

[SASKiwi · Accepted solution]

@banaz- If it is only 4 tables it is probably easiest to shorten the names, so that they show via a SAS LIBNAME. SQL passthru would allow you to read the longer version names, but they would still not appear via a LIBNAME.

See this post for more details: https://communities.sas.com/t5/SAS-Data-Management/Long-SQL-Server-via-ODBC-table-names-amp-column-n...

Increasing the 32 character table and column name limit is planned for SAS 9.5, which is likely to be available in the next 12 months or so.

[banaz]

@SASKiwi Thank you. Luckily, I was able to ask IT to shorten the names of the tables. I am now able to import these tables into SAS library.