topic Re: Importing a table with long column names from SQL Server to Enterprise Guide in SAS Enterprise Guide

Importing a table with long column names from SQL Server to Enterprise Guide

sethuraman_ram — Thu, 28 Apr 2016 17:23:18 GMT

Has anyone faced problems while loading an SQL table with long column names? When i try to drag a table having long column names onto a process flow from a library , I get the error that an Open data operation and the error is that the variable < long column name> is not foind in the file.

It appears to me that the SAS/Access engine for SQL server is not able to recognize long column names. If anyone has experienced this, would love to know what workarounds are avaialbe for this issue.

Thanks,

Sethu

Re: Importing a table with long column names from SQL Server to Enterprise Guide

Reeza — Thu, 28 Apr 2016 17:42:28 GMT

One workaround is to create a view that has the variable renamed to something that's shorter.

Then load the view.

Re: Importing a table with long column names from SQL Server to Enterprise Guide

SASKiwi — Thu, 28 Apr 2016 19:41:34 GMT

Try this SAS option: options validvarname = V7;

This should truncate on-the-fly any long SQL Server column names to no more than 32 characters.

Re: Importing a table with long column names from SQL Server to Enterprise Guide

sethuraman_ram — Thu, 28 Apr 2016 20:01:59 GMT

Thanks for the reply.

I also posed this question to SAS technical support and they came back with the following solution:

For the long column names, in EG go to Tools>Options>SAS Programs.

Put a check mark beside "Submit SAS code when server is connected" and click the Edit box. Enter:

options validvarname=v7;"

This has the effect of truncating the long variable names but it allows me to proceed with defining tasks on EG

Re: Importing a table with long column names from SQL Server to Enterprise Guide

SASKiwi — Thu, 28 Apr 2016 20:56:18 GMT

Cool - problem solved. Please be aware the 32 character limit also applies to table names. If you have table names > 32 characters they will not show in your data libraries / server list at all and you will need to use SQL passthru to read them.