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

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???

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

8 REPLIES 8
Ksharp
Super User
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
Obsidian | Level 7

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

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

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

ljim1075_1-1720613337615.pngljim1075_2-1720613352234.png

 

Tom
Super User Tom
Super User

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
Obsidian | Level 7
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
Super User Tom
Super User

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
Obsidian | Level 7
Thank you Tom, this worked!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 323 views
  • 5 likes
  • 4 in conversation