special character in values for sql procedure

[makarand]

Hi,

I need to run sql procedure which has a value in 'T05/000023/14-15' but I'm getting syntex error - ERROR: CLI prepare error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '/'.
SQL statement: execute SAS_INV_RPT AMRUTFAC, SDAB, 1, TN05/000023/14-15, TN05/000024/14-15.

INVNO => T05/000023/14-15

Does anyone knows the workaround?

additional info what i impliment (can be improved if you think so)

we need to use this sql procedure to use in sas application (sas va).> hence i guess need to use query builder (ie stored process)

can not change the variable name in database.

it is a sql proceduere hence need to use explicit pass

Can we change the variable names inbetween the DB and before passing the query?

%LET uid = "***"; 
%LET pwd = "********"; 
 
* options VALIDVARNAme=v7;
PROC SQL; 
CONNECT TO ODBC (DSN='AHCLODBC' uid=&uid pwd=&pwd); 
CREATE TABLE sas_inv_rpt AS 
		SELECT * FROM connection to ODBC (execute SAS_INV_RPT &COMPANY_CODE, &LOCATION_CODE, &LANG_ID, &INVNO_min, &INVNO_max); 
DISCONNECT FROM ODBC; 
QUIT; 
RUN;

[RW9]

Question 1 is going to be, why do you have a column name T05/000023/14-15 in your database, that to my sounds like bad practice straight off.  

Second question is what are you trying to do, it looks like your getting the database to run something, and then feedback results to SAS?  Why not just create a view in the dataset that sets up the data as you want and then pull that from the database?  I don't understand why your driving one application with another, just adds a whole load of unecessary complexity.  I wouldn't for instance, setup a database with data entry screens then start sending data over via Excel insert into database?

[Kurt_Bremser]

If I ever saw a euphemism, "bad practice" in this context is one. Who came up with the (expletives deleted) idea to use mathematical symbols in a column name?

Get the database design straight, as it will spare you (and many others) LOTS of grief in the future.

[makarand]

Thanks for reply RW9 and KurtBremser,
special thanks for the concern,
I am working with a client DB, so not my privilege to change the DB.
ans1. T05/000023/14-15 is not a column name but value which is used by parameter in stored process for a column named INVNO.
ans2. what you say is right. I want to pull data with SAS-VA from sql with given parameters (location, company, language and invno range etc)

[makarand]

PS: sorry. wrong file attached caused the misunderstanding

[RW9]

So you have a range: "T05/000023/14" -"T05/000023/15", i.e. the data element in INVNO shoul be one or the other of those two, then something like:
where INVNO="T05/000023/14" or INVNO="T05/000023/15"

I think the problem you are facing is that the character string "T05/000023/14" is not numeric, so you can't do ranges on it.  If you pull all the data into SAS (i.e. no where clause) then you could process the data and filter the data in SAS very easliy.  However we don't know what this:

execute SAS_INV_RPT &COMPANY_CODE, &LOCATION_CODE, &LANG_ID, &INVNO_min, &INVNO_max)

Is actually doing or requires.  I don't use VA so don't know about that, but when I need data from a database I would generally just pull all the data, then process in SAS.  

[ballardw]

Since your values are character then "range" comparison is extremely slippery. If the data is really tightly constrained it may work but please examine the following and see if the "range" behaves the way you would want.

data junk;
   infile datalines truncover;
   informat x $15.;
   input x $ 1-15;
   if  'AB' le x le 'CD' then y='In Range';
datalines;
abc123      
Abc123
ABC1234
ABCC1234
;
run;