Date:         Wed, 15 Apr 2009 08:58:52 -0700
Reply-To:     Emma Gottesman <emma.hill.42@GOOGLEMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Emma Gottesman <emma.hill.42@GOOGLEMAIL.COM>
Organization: http://groups.google.com
Subject:      Resolving macro variables when using SQL pass-through
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

I'm using sql pass-though to an oracle database.
Some of my variables are strings so I want to do something along the
lines of:

proc sql;
  connect to oracle (user=<user_name> orapw=<password>
path='@<path>');
  create table LocalTable as
  select * from connection to oracle
  (
    select
     alias.field1,
     alias.field2
    from
      schema.table_name alias
   where
     alias.string_field = "&macro_var."
   );
disconnect from oracle; quit;

Only my macro variable won't resolve. I think (but I'm not sure) it's
to do with the fact that oracle doesn't like the double quotes, but
surely if I use single quotes then SAS will complain.

How do I get deal with this?  For technical reasons I'd prefer to use
pass-though rather than a libname statement.

regards
--Emma