BookmarkSubscribeRSS Feed
makarand
Obsidian | Level 7

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;

 

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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
Obsidian | Level 7
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
Obsidian | Level 7
PS: sorry. wrong file attached caused the misunderstanding
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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;

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!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 2305 views
  • 1 like
  • 4 in conversation