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

[sethuraman_ram]

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.

[DartRodrigo]

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

[sethuraman_ram]

@DartRodrigo

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.

[DartRodrigo]

Ok, 

I've found this similar Usage Note 48566: A CLI error is issued when you attempt to create and load data into a temporary SQ... post or Problem Note 42504: Special characters in DBMS column names cause problems with the explicit pass-th....

Hope this helps

[sethuraman_ram]

Thanks @DartRodrigo.

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.

[Reeza]

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

[Reeza]

Run a proc contents and check the output. 

If you can post it. 

[sethuraman_ram]

@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.

[Ksharp]

Try add one more option:

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

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

[sethuraman_ram]

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.

[DartRodrigo]

@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;

[RW9]

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.

[SASKiwi · Accepted solution]

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.

[sethuraman_ram]

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.