Desktop productivity for business analysts and programmers

Report of Table names in a library

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Report of Table names in a library

Hi Guys,

I'm stuck as far as to why one of my tables (from my SQL Database) isn't coming through into my library. Almost everything else is. I'm looking to QA it. Try and work out if there are any other tables that are missing.

So my question: how can I produce a list of all the tables that are in a SAS library that I've created?

Thanks,

Bree


Accepted Solutions
Solution
‎05-08-2013 11:40 PM
Super User
Posts: 19,170

Re: Report of Table names in a library

Try proc datasets.

check how long the table name is, if its over 32 characters it won't show up.

View solution in original post


All Replies
Solution
‎05-08-2013 11:40 PM
Super User
Posts: 19,170

Re: Report of Table names in a library

Try proc datasets.

check how long the table name is, if its over 32 characters it won't show up.

Contributor
Posts: 20

Re: Report of Table names in a library

it's over 32 chars long!

Is there a work around for this? or do I need to go back to the IT team?

Super User
Posts: 3,238

Re: Report of Table names in a library

One workaround would be to query the "dictionary" tables of the source SQL database. Which SQL database are you using, for example Oracle, SQL Server, as each database names its dictionary tables differently.

Community Manager
Posts: 2,889

Re: Report of Table names in a library

If you want to see what's in the database (and not just what you see in a SAS library), try this technique:

Using PROC SQL to get the schema of a MySQL database - The SAS Dummy

Even though the article is about MySQL, it can apply to other databases as well.

Chris

Respected Advisor
Posts: 3,156

Re: Report of Table names in a library

Hi Chris,

Information schema is not supported by Oracle?

I have been using something like ALL_VIEWS, ALL_TABLES, ALL_TAB_COLUMNS to query Oracle Metadata.

Haikuo

Community Manager
Posts: 2,889

Re: Report of Table names in a library

Yes, different databases have different schema mechanisms.  My example works with MySQL and a few others.  I just wanted to point out that you can use PROC SQL to pass a database-specific request to get information as needed.

Chris

Super User
Posts: 19,170

Re: Report of Table names in a library

You will be able to access the table using SQL Pass Thru or you can ask IT to change the name to less than 32 characters. It depends on what your needs are, what the best solution would be.

Contributor
Posts: 20

Re: Report of Table names in a library

It looks like the boys are going to build a DB for me, Basically views so I can access all the data, and we'll rename everything.

The same goes for Field names right? If I have a table with a field name that is >32 Char, what happens? does it just neglect to import that field or does it cause the whole table import to fail? We have plenty of Table names >32 Char.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 790 views
  • 3 likes
  • 5 in conversation