Date:         Thu, 22 May 2008 11:32:50 -0500
Reply-To:     "data _null_," <datanull@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "data _null_," <datanull@GMAIL.COM>
Subject:      Re: Put single quotes on a macro parameter passed from Call
              Execute
Comments: To: Jack Clark <JClark@chpdm.umbc.edu>
In-Reply-To:  <456B52C41B724C41B96561D7AD283E7DEFE647@mail.chpdm.umbc.edu>
Content-Type: text/plain; charset=ISO-8859-1

I'm no quoting expert or any other kind.  But I think %BQUOTE can
solve the problem.  See below.

%macro putlabs (vname=,vlab=);
   proc sql;
      connect to odbc as mydb (datasrc="wf_test_db");
      execute
         (sys.sp_addextendedproperty
         @name=N'Caption'
         ,@value=N%bquote('&vlab.')  /*here*/
         ,@level0type=N'SCHEMA'
         ,@level0name=N'dbo'
         ,@level1type=N'TABLE'
         ,@level1name=N'pharm07_d'
         ,@level2type=N'COLUMN'
         ,@level2name=N%bquote('&vname.') /*here*/
         by mydb
         ;
      quit;
   %mend putlabs;



On 5/22/08, Jack Clark <JClark@chpdm.umbc.edu> wrote:
> Hello,
>
>
>
> I am using CALL EXECUTE in a DATA step to call a macro program which
> uses SQL pass-through to execute a SQL Server 2005 stored procedure.  I
> need to pass two of the data set variables as macro parameters.  The
> problem I am having is that the stored procedure has to have these
> parameters in SINGLE quotes.  Initially, I used double quotes in the
> stored procedure syntax because I needed double quotes so the macro
> parameters would resolve - but I got a syntax error from SQL Server.
>
>
>
> Now, I am trying to think of how I could add the single quotes to the
> data set variables, so they are passed as part of the macro parameter.
> Or, could I use a strategy to send the parameter without quotes and
> build a string that includes a single quote on each end of the resolved
> parameter (%sysfunc(cats("'",&vlab.,"'")) did not work).
>
>
>
> I think I need a macro quoting expert!
>
>
>
> This is what I have (which isn't working)..
>
>
>
> * macro to create captions for SQL Server fields from SAS variable
> labels ;
>
> * (uses SQL pass-through facility) ;
>
> *    vname - variable name ;
>
> *    vlab  - variable label ;
>
> %macro putlabs (vname=,vlab=);
>
>
>
> proc sql;
>
>   connect to odbc as mydb (datasrc="wf_test_db");
>
>   execute
>
>       (sys.sp_addextendedproperty
>
>       @name=N'Caption'
>
>       ,@value=N"&vlab."
>
>       ,@level0type=N'SCHEMA'
>
>       ,@level0name=N'dbo'
>
>       ,@level1type=N'TABLE'
>
>       ,@level1name=N'pharm07_d'
>
>       ,@level2type=N'COLUMN'
>
>       ,@level2name=N"&vname."
>
>       by mydb
>
>      ;
>
> quit;
>
>
>
> %mend putlabs;
>
>
>
> * create table of sas data set variables and labels ;
>
> proc sql;
>
>   create table pharm07_labs as
>
>   select name, label
>
>   from   dictionary.columns
>
>   where  libname = "WFD" and
>
>          memname = "PHARM07_D"
>
>   ;
>
> quit;
>
>
>
>
>
> * add captions to SQL Server fields ;
>
> data _null_;
>
>   set pharm07_labs;
>
> call execute('%putlabs(vname='||name||',vlab='||label||')');
>
> run;
>
>
>
>
>
> The CALL EXECUTE - as written - is properly sending the parameters, but
> the stored procedure is crashing because I am using double quotes around
> the macro variable names.
>
>
>
> Thanks in advance.
>
>
>
>
>
> Jack Clark
>
> Research Analyst
>
> Center for Health Program Development and Management
>
> University of Maryland, Baltimore County
>
>
>
>
>