Date:         Mon, 24 Jun 2002 12:31:31 -0400
Reply-To:     Ed Heaton <EdHeaton@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ed Heaton <EdHeaton@WESTAT.COM>
Subject:      Re: Macro parameter quoting
Comments: To: "Krone, Ben --- Manager - Operations Systems --- GO"
          <Ben.Krone@FREIGHT.FEDEX.COM>
Content-Type: text/plain; charset="iso-8859-1"

Ben, I know this will involve more data transfer, but you might try the
following.

%macro junk(location) ;
   Data out1 ;
      Set master ;
      Where ( location eq "&location." ) ;
   Run ;
   Proc sql ;
      Connect to db2 (ssid=dsn) ;
      Create table out2 as
         select *
         from connection to db2(
            select * from db2table for fetch only
         )
         where ( location eq "&location" )
      ;
      Disconnect from db2 ;
   Quit ;
%mEnd junk ;
%junk(ABC)

I don't know what the "for fetch only" clause does, but I assume it works.
In this code, I let SAS's SQL do the subsetting rather than DB@'s SQL.

Ed

Edward Heaton, Senior Systems Analyst,
Westat (An Employee-Owned Research Corporation),
1550 Research Boulevard, Room 2018, Rockville, MD 20850-3195
Voice:  (301) 610-4818                      Fax:  (301) 294-3992
mailto:EdwardHeaton@westat.com              http://www.westat.com


-----Original Message-----
From: Krone, Ben --- Manager - Operations Systems --- GO
[mailto:Ben.Krone@FREIGHT.FEDEX.COM]
Sent: Monday, June 24, 2002 11:24 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Macro parameter quoting


Greetings,

I am certain that there is an easy answer that I have overlooked, but I am
having a problem with applying quotes to a parameter passed to a macro. I
have it working in data step code, but when trying to use SQL pass-though
query against DB2, it keeps failing due to double quotes around the
parameter.

Here is an example of my situation

%macro junk(location);

data out1;
  set master;
  where location = "&location."; /* resolves to "ABC", this works fine as
data step accepts double quoted strings */
run;

proc sql;
  connect to db2 (ssid= dsn);
  create table out2 as select * from connection to db2
  (select *
     from db2table
   where location = "&location."  /* resolves to "ABC", this fails as DB2
does not like double quoted strings, need 'ABC' */
   for fetch only);
  disconnect from db2;
quit;

%mend junk;

%junk(ABC)

How can I force single quotes around a macro parameter?

Any advice is greatly appreciated.
Thanks
Ben


**********************************************************
This message contains information that is confidential
and proprietary to FedEx Freight or its affiliates.
It is intended only for the recipient named and for
the express purpose(s) described therein.
Any other use is prohibited.
****************************************************************