05-24-2016 04:30 AM - edited 05-24-2016 05:16 AM
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;
05-24-2016 04:43 AM
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?
05-24-2016 04:52 AM
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.
05-24-2016 05:12 AM
05-24-2016 05:48 AM
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.
05-24-2016 11:24 AM
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;