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

 

I'm getting the following error when I run this SAS code.  The backend database is SQL Server (13.0.4422.0) connecting via ODBC.  They used a restricted keyword as a column name, which is causing the error.  I'm not sure the implications of changing the column name.  Is there a way to modify my sas so I can keep the keyword column name.  BTW: This runs under 9.2.  I'm porting the code to 9.4. Also, I tried enclosing constraint with backets [constraint]

 

proc sql noprint;

                               select distinct constraint into :PBD_binary_constraints separated by " " from warehse.cmmn_opt_assetbounds

                                                where event_multiplier = 0 or event_multiplier = .;

quit;

 

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

        keyword 'constraint'.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

It's Microsoft's own ODBC driver that chokes it seems.

 

Maybe try

  select distinct constraint as CONS

or

  select distinct 'constraint'n as CONS

 

Otherwise explicit passhtrough should work:

proc sql;

 connect using warehse;

 select * from connection to warehse (select distinct constraint as CONS..);

View solution in original post

4 REPLIES 4
kiranv_
Rhodochrosite | Level 12

I am not very sure about this. but try

options NODBIDIRECTEXEC;

 

You can create a sas table in explicit pass through with a new column name and then try the query below with SAS table

ChrisNZ
Tourmaline | Level 20

It's Microsoft's own ODBC driver that chokes it seems.

 

Maybe try

  select distinct constraint as CONS

or

  select distinct 'constraint'n as CONS

 

Otherwise explicit passhtrough should work:

proc sql;

 connect using warehse;

 select * from connection to warehse (select distinct constraint as CONS..);

RichM
Calcite | Level 5

Please note adding the modifying the sql didn;t work.  I was getting the same error

 

Maybe try

  select distinct constraint as CONS

or

  select distinct 'constraint'n as CONS

 

The explicit passhtrough worked but I had to pull back the whole table (luckily, not a big table)

proc sql;

 connect using warehse;

 select * from connection to warehse (select distinct constraint as CONS..);

 

 

 

Kurt_Bremser
Super User

So someone used a variable name in MS SQL server that is invalid there and causes the MS ODBC connection to crash.

The correct remedy is to not use the keyword "constraint" as a variable name, which is sound programming practice in the first place.

Have the mistake fixed on the SQL server side in the database.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 3661 views
  • 2 likes
  • 4 in conversation