Desktop productivity for business analysts and programmers

Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives an error.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives an error.

Hello, 

 

I am running into problems working with DBMS column names that have a "/" in them. I noticed that when the library was defined in metadata and the table containing the column name with the "/" was registered, SAS changed the name of the DBMS column that had a "/" in it to an "_". However, when I run a proc means statment using this column as a classification variable, the SQL access driver is not able to interpret this variable and gives me a driver error 

 

"CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid column name 'Coated'. :
[SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid column name 'Insulated'. : [SAS][ODBC SQL Server
Wire Protocol driver][Microsoft SQL Server]Statement(s) could not be prepared."

 

For context to the error above, the column name on the DBMS is "Coated/Insulated". When registering the table in metadata, SAS changed the variable name to "Coated_Insulated". The error above was thrown while executing the following statement:

"PROC MEANS DATA=RS.tbldimProduct
FW=12
PRINTALLTYPES
CHARTYPE
NWAY
VARDEF=DF
MEAN
STD
MIN
MAX
N ;
VAR product_name_id;
CLASS Coated_Insulated / ORDER=UNFORMATTED ASCENDING;
RUN;"

 

Is this error specific to the SAS/Access engine 9.4 and is there a workaround?

Incidentally, I have set the VALIDVARNAME to v7.

 


Accepted Solutions
Solution
‎05-26-2016 03:31 PM
Super User
Posts: 3,233

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

When you try to query non-standard or longer than 32 character DBMS column names the only reliable and sometimes the ONLY method to use is SQL PASSTHRU.

 

In the case of the PROC MEANS, SAS has to construct an SQL query behind the scenes somehow referencing the non-standard column names which I suspect all gets a bit hard. Perhaps you could try the 'Coated/Insulated'n name in a simple SQL query. Does that work?

 

proc sql;
  create table test as
  select count(*)
        ,'Coated/Insulated'n
  from RS.tbldimProduct
  group by 'Coated/Insulated'n
  ;
quit;

 

You should be able to get close to what the PROC MEANS does using SQL PASSTHRU and GROUP BY.

View solution in original post


All Replies
Regular Contributor
Posts: 212

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

Hi mate.

 

You can create columns with the this sintax "///var &**&& da"n.

The n at the end means the end of the label.

data test;
   length "/inp1 id1"n 3 "/name2 id2"n $ 20;
   input "/inp1 id1"n "/name2 id2"n;
   cards;
  1 Rod
  2 Chad
  3 Gave
  4 Mick
  ;
run;

So i can specify columns with spaces and special characters at the declaration.

 

Hope this helps

Occasional Contributor
Posts: 12

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

@DartibaliRodrigo

Thanks for your tip. Unfortunately it did not work out even when i defined the name literal. It still threw up the error that "Coated/Insulated" was not a valid SAS name.

 

Regular Contributor
Posts: 212

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

[ Edited ]
Occasional Contributor
Posts: 12

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

Thanks @DartibaliRodrigo.

The problem note described in http://support.sas.com/kb/42/504.html closely resembles the situation that i am currently experiencing althought it is mentioned that the error was fixed 9.3 TS1M0.

I am currently running 9.4. I have opend up a tech track for this issue to have SAS technical support take a look at this.

 

Super User
Posts: 19,093

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

SAS Studio, SAS EG , and BASE SAS may have different settings for validvarname. Check what the option is set to in each environment. 

Super User
Posts: 19,093

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

Run a proc contents and check the output. 

If you can post it. 

Occasional Contributor
Posts: 12

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

@Reeza

I have attached the output of the proc contents. The offending varaible is #46 and in the second row of the alphabetical list of variables. The interesting this is that if I run the proc contents within enterprise guide I get the variable name as Coated_Insulated. However, when i run the same procedure in SAS Studio I get the variable name as Coated/Insulated.

 

Thanks for your help.

 

Super User
Posts: 9,867

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

Try add one more option:

libname x odbc dsn=....  preserve_col_names=yes  ;

 

and use 'xx/yy'n refer to it at proc means.

Occasional Contributor
Posts: 12

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

Thanks @Ksharp.

I tried this option too but ended up with the error since the Database driver is not able to read the SAS name literal. My suspicion is that SAS/Access driver for SQL Server might be the one causing the problems here.

Regular Contributor
Posts: 212

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

@sethuraman_ram

 

Try:

Change the part of proc means from this CLASS Coated_Insulated / ORDER=UNFORMATTED ASCENDING;

To: 

CLASS "Coated/Insulated"n / ORDER=UNFORMATTED ASCENDING;

Super User
Super User
Posts: 7,711

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

Personally I would say this is not your problem, it is the problem of the person who setup a database with non standard characters.  Every role I have come across/people who build databases would never use anything other alphabetic charcters, undersore, and numbers.  The reason you have hit upon here.  If it was me I would go back to the source and get them to change - I know sometimes this is difficult, however if you don't raise it, whats to stop them using other characters, non printable ones for instance.  Will you try to code around every problem you get?  This should all be in documentation - transfer specs, database specs, standard operating proceures etc. these are all here to stop problems like this.

Solution
‎05-26-2016 03:31 PM
Super User
Posts: 3,233

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

When you try to query non-standard or longer than 32 character DBMS column names the only reliable and sometimes the ONLY method to use is SQL PASSTHRU.

 

In the case of the PROC MEANS, SAS has to construct an SQL query behind the scenes somehow referencing the non-standard column names which I suspect all gets a bit hard. Perhaps you could try the 'Coated/Insulated'n name in a simple SQL query. Does that work?

 

proc sql;
  create table test as
  select count(*)
        ,'Coated/Insulated'n
  from RS.tbldimProduct
  group by 'Coated/Insulated'n
  ;
quit;

 

You should be able to get close to what the PROC MEANS does using SQL PASSTHRU and GROUP BY.

Occasional Contributor
Posts: 12

Re: Working with DBMS column names that have a "/" in them in SAS enterprise Guide gives a

Thanks @SASKiwi. This was the only way I could get past the error. SAS technical support suggested that I include the following options in the odbc.ini file:

Quotedid=Yes
EnableQuotedIdentifiers=1

 

However, these did not eliminate the error.

 

 

☑ This topic is solved.

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

Discussion stats
  • 13 replies
  • 849 views
  • 11 likes
  • 6 in conversation