Column Name Matches SAS' FEDSQL Reserve Words

[ljim1075]

Hello everyone!  I'm counting total number of distinct values from all columns from various tables.  What I've come across is that some of the column name matches that of SAS' Reserve Words.  So the questions is simple, how can I query based on these words; i.e. add, tran, user, etc.  Sample of error msg is: 

ERROR: CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near the keyword

'ADD'.

PROC SQL;
CREATE TABLE Dist_Val_ADD AS
SELECT
"ADD" AS Column_Name,
COUNT(DISTINCT ADD) as Num_Dist_Values FORMAT COMMA10.
FROM CONN.&TBLNM.
;
QUIT;

I've also tried:

• COUNT(DISTINCT CATS("A.",ADD)) as Num_Dist_Values FORMAT COMMA10.
• COUNT(DISTINCT CATS("[",ADD.,"]")) as Num_Dist_Values FORMAT COMMA10.
• COUNT(DISTINCT CATS("'",ADD,"'N")) as Num_Dist_Values FORMAT COMMA10.
• COUNT(DISTINCT CATS("'",ADD,"'")) as Num_Dist_Values FORMAT COMMA10.
• COUNT(DISTINCT CATS('"',ADD,'"')) as Num_Dist_Values FORMAT COMMA10.

All have failed and resulted in the same error msg.  The table is in SQL Server, but may have other table in other platforms.

Any other suggestions???

[Ksharp]

Rename it before referring to it ?

PROC SQL;
CREATE TABLE Dist_Val_ADD AS
SELECT
_ADD ,
COUNT(DISTINCT _ADD) as Num_Dist_Values FORMAT=COMMA10.
FROM CONN.&TBLNM.(rename=(ADD=_ADD))
;
QUIT;

[ljim1075]

Thank you Ksharp for your suggestion, I know that _ADD will work, the issue lies that this is running in a un-monitored DO LOOP and the code will have failures that I would have to manually update one by one.  I want to automate as much as possible.  My thought if there's a SAS backend table with the list of reserve words then I can make a comparison and replace those that matches the reserve words.  Hopes this makes sense...

[Kurt_Bremser]

The ERROR does not come from SAS. It originates in the MS SQL server, so it seems someone managed to create an illegal column name there. Or the column isn't even there in the first place; you may have mistaken a label for a name.

PS run PROC CONTENTS on CONN.&TBLNM

[ljim1075]

Kurt, the field is in the table, notice the fields Tran and Add in this particular table

[Tom]

Not sure why you mentioned PROC FEDSQL since you don't seem to be using it.

Normal PROC SQL has an option to treat strings in double quotes as variable names.  So any actual string literals have to use single quotes.

PROC SQL dquote=ansi;
CREATE TABLE Dist_Val_ADD AS
SELECT
   'ADD' AS Column_Name
 , COUNT(DISTINCT "ADD") as Num_Dist_Values FORMAT COMMA10.
FROM CONN.&TBLNM.
;

[ljim1075]

Tom, in general what I'm trying to do is to inquiry on a column name that matches SAS overall reserve words, for example, user, date, add, etc. My issue is primarily on the COUNT(DISTINCT "ADD") as Num_Dist_Values FORMAT COMMA10. I've added the bquote option and that didn't work either

[Tom · Accepted solution]

It is NOT a problem for SAS.  Try it with an actual SAS dataset.

data add;
  input add ;
cards;
1
2
3
;

proc sql;
 select count(distinct add) as n_add 
 from add
;
quit;

So your problem is related to how SAS is converting the code into SQL it can run in the foreign database that the libref your original code was using was pointing into.

I would look into seeing if there are libname options you can use to force SAS to use syntax to make valid variable names.

If you really cannot find a solution to make SAS automatically make valid syntax you will have to resort to writing your own SQL that works in that database.  I think your previous error message seemed to indicate that it is Microsoft SQL Server.  In that case I think they use [] around names.  So perhaps you could get the job done by running code like this: 

libname mylib .... ;
proc sql;
connect using mylib;
create table want as
select 'ADD' as varname, Num_Dist_Values format=comma10.
from connection to mylib(
  select COUNT(DISTINCT [ADD]) as Num_Dist_Values 
  from mysql_table_name
);
quit;

[ljim1075]

Thank you Tom, this worked!