Pass string to database through ODBC

Accepted Solution Solved
Reply
Super Contributor
Posts: 418
Accepted Solution

Pass string to database through ODBC

Hello everyone. I am trying to pass a macro variable to a database using an odbc connection in Base sas. HOwever You cannot put double quotes around the macro variable (to symbolize a character value) because sql server does not recognize double quotes (need single quotes).  Below please see what I have, and below that is what I need the macro to resolve too! ANy help is greatly appreciated!

%let Clientnamevar=GreenField;

What I have

proc sql;

connect to odbc as myodbc (dsn=MYDSN);

create table answer as  select * from connect to myodbc

(select *

from sqlservertable

where Clientname="&clientnamevar.");

quit;

run;

What I want the code to run.

proc sql;

connect to odbc as myodbc (dsn=MYDSN);

create table answer as  select * from connect to myodbc

(select *

from sqlservertable

where Clientname='GreenField');

quit;

run;

I can get it to work with numeric variables, and I KNOW there is a funcition to do this (%bqoute or something like that) but no matter what I try I can't seem to get it to work.

Sorry if this is an overly simplistic question, and thanks a bunch for your help!

Thanks

Brandon


Accepted Solutions
Solution
‎03-05-2014 02:24 PM
Super User
Posts: 5,500

Re: Pass string to database through ODBC

Posted in reply to Anotherdream

Brandon,

The simple way to do this is when assigning a value to &Clientnamevar:

%let Clientnamevar='Greenfield';

Then you won't need to add quotes later.  But if that's not possible, here's the string to use to replace "&Clientnamevar":

%unquote(%str(%'&Clientnamevar%'))

Good luck.

View solution in original post


All Replies
Solution
‎03-05-2014 02:24 PM
Super User
Posts: 5,500

Re: Pass string to database through ODBC

Posted in reply to Anotherdream

Brandon,

The simple way to do this is when assigning a value to &Clientnamevar:

%let Clientnamevar='Greenfield';

Then you won't need to add quotes later.  But if that's not possible, here's the string to use to replace "&Clientnamevar":

%unquote(%str(%'&Clientnamevar%'))

Good luck.

Super Contributor
Posts: 418

Re: Pass string to database through ODBC

Posted in reply to Anotherdream

Thanks astounding. I know about the quotes in the let, but sadly I need a lot of these variables to not have quotes (for all kinds of reasons).

I like the unquote solution so I am marking it correct!

Brandon

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 600 views
  • 0 likes
  • 2 in conversation