f



Re: SAS/ACCESS Libname: Reading Tables with Names Longer than 32 #6

Does this LIBNAME option have any effect on your problem?

preserve_tab_names=yes

On 8/27/09, Paul Miller <pjmiller_57@yahoo.com> wrote:
> Hi Dan, and Art, and others,
>
> Yes, the problem is that the SQL Server table names are too long. My libname
>
> libname sqllib oledb provider=SQLOLEDB datasource='xxxx' user=xxxx password=xxxx properties=("Initial Catalog"=xxxx)
> prompt=yes schema=dbo dbmax_text=32767;
>
> only allows me to read tables with names up to 32 characters long.
>
> The problem is that I'm routinely receiving SQl Server databases that have about a hundred tables with names that go beyond the 32 character limit. My understanding is that, when accessing SQL Server tables using a libname, SAS will automatically truncate variable names that are too long, and that it will take steps to ensure that the new variable names are unique. So my hope would have been that it would do the same thing with long variable names, but, alas, this does not appear to be the case.
>
> I can see a number of ways of dealing with this. But so far they all involve manually renaming large number of tables every time I want to do an analysis. And for obvious reasons I'd prefer not to do that.
>
> So I was hoping someone might know of some way to automatically rename the tables. I wouldn't care too much what they were called, as long as they had unique names that would give me some sense of the table's contents. For example, take the two table names below:
>
> avw_DRUG_ADMIN_sctGemcitabineAdministration_mcmaGemcitabineReasonDelayed
> avw_DRUG_ADMIN_sctGemcitabineAdministration_mcmaGemcitabineReasonReduced
>
> I'd be happy if I could automatically rename these tables as
>
> avw_DRUG_ADMIN_sctGemcitabineAd1
> avw_DRUG_ADMIN_sctGemcitabineAd2
>
> At least I'd know that the tables contained information about Gemcitabine administration. And I think it would be easy to keep track of exactly what information about Gemcitabine administration was contained in each table.
>
> Does anyone have any sense of how to do that?
>
> Alternatively, I'd be interested in figuring out what might be the least painful way of renaming and then reading my tables.
>
> Dan, you mentioned the possibility of assigning an alias to each table when working with Oracle. Would this involve submitting code to Oracle via SAS? If so, could you show me some sample code so I can see if this would be better than using the sql pass through?
>
> Thanks,
>
> Paul
>
>
>      __________________________________________________________________
> Get a sneak peak at messages with a handy reading pane with All new Yahoo! Mail: http://ca.promos.yahoo.com/newmail/overview2/
>
0
iebupdte (1706)
8/27/2009 5:15:27 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

0 Replies
609 Views

Similar Articles

[PageSpeed] 47

Reply: