BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sethuraman_ram
Obsidian | Level 7

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

13 REPLIES 13
DartRodrigo
Lapis Lazuli | Level 10

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
Obsidian | Level 7

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

 

sethuraman_ram
Obsidian | Level 7

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
Super User

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

Reeza
Super User

Run a proc contents and check the output. 

If you can post it. 

sethuraman_ram
Obsidian | Level 7

@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
Super User

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
Obsidian | Level 7

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
Lapis Lazuli | Level 10

@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
Diamond | Level 26 RW9
Diamond | Level 26

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
PROC Star

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
Obsidian | Level 7

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.

 

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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