Date:         Mon, 12 Mar 2007 14:46:14 -0400
Reply-To:     "data _null_;" <datanull@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "data _null_;" <datanull@GMAIL.COM>
Subject:      Re: insert in to Oracle with Single quote
Comments: To: Dianne Rhodes <diannerhodes@comcast.net>
In-Reply-To:  <200703121824.l2CH1LDB023973@mailgw.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Perhaps if you can "double" the single quote when the value of
TREE_TITLE is created all will work.  This example may be similar to
what you are doing. The TRANWRD turns a single quote into two single
quotes.

1          data _null_;
2             call symput('tree_title1',tranwrd("Fisher's Exact","'","''"));
3             run;

NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


4          %put _user_;
GLOBAL TREE_TITLE1 Fisher''s Exact
5
6          %let i = 1;
7          %put NOTE: %bquote('&&tree_title&i');
NOTE: 'Fisher''s Exact'
8
9
10         data _null_;
11            x = %unquote(%bquote('&&tree_title&i'));
12            put 'NOTE: ' x=;
13            run;

NOTE: x=Fisher's Exact
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


On 3/12/07, Dianne Rhodes <diannerhodes@comcast.net> wrote:
> I have a macro utility I am trying to add a field, I will copy the
> entirety of it below my sig.  The problem I am running into is it does an
> insert
>
>   %do I = 1 %to &count_add;
>
>         exec (insert into PPI_INDEX
>         (PPI_Index_sid,
>          tree_title)
>         )
>             values (ppi_index_seq.nextval,
>                     %bquote('&&tree_title&i')
>  )
>  ) by oracle;
>
> The problem is that the text for tree_title contains a single quote.
> Oracle tells me Oracle execute error: quoted string not properly
> terminated.
>
> I'm sure some of you guys have run into this before.  I am guessing from
> some other code here that the solution may be to use %unquote around the
> string.  Any one know this one?  Please reply to the list or to my work
> address
>
> rhodes dot dianne @ bls dot gov
>
> /********************************************************************/
> /** PROGRAM NAME:   inspix.sas            */
> /** FUNCTION:  Insert ppi index sid        */
> /** DATE WRITTEN:  02/14/06   UPDATED: 02/14/06                     */
> /** January 2007    Dianne Rhodes modified for UC 9-4              **/
> /**                  removed schema only valid in libname           **/
> /********************************************************************/
> %macro inspix (inds=);
>
>   proc sql noprint;
>     select count(*) into :count_add
>     from &inds;
>   quit;
>
>   %if &count_add > 0 %then %do;
>
>     data _null_;
>       set &inds;
>       call symput('tree_id'||left(_N_),tree_id);
>       call symput('index_id'||left(_N_),index_id);
>       call symput('index_type'||left(_N_),index_type);
>       call symput('tree_category'||left(_N_),tree_category) ;
>       call symput('index_version_num'||left(_N_),index_version_num);
>
>       call symput('ppi_index_code'||left(_N_),ppi_index_code);
>       call symput('index_start_irm' ||left(_n_),  index_start_irm) ;
>       call symput('index_base_period' ||left(_n_),  index_base_period) ;
>       call symput('official_pub_title' ||left(_n_),  official_pub_title) ;
>
>     run;
>
>    options nomprint;
>     proc sql ;
>       connect to oracle (user= &USER password= &PWD path= &PATH ) ;
>                        ** schema was ignored in V8, causes an error in
> V9   schema= &SCHEMA)  ;
>
>    %do I = 1 %to &count_add;
>
>         exec (insert into PPI_INDEX
>                    (PPI_Index_sid,
>                     tree_id,
>                     index_id,
>                     index_type,
>                     tree_category,
>                     index_version_num,
>                      ppi_index_code,
>                      index_start_irm,
>                      index_base_period,
>                      official_pub_title
>                      )
>             values (ppi_index_seq.nextval,
>                     %bquote('&&tree_id&i'),
>       %bquote('&&index_id&i'),
>                     %bquote('&&index_type&i'),
>       %bquote('&&tree_category&i'),
>       &&index_version_num&i,
>       %unquote(%bquote('&&ppi_index_code&i')),
>       %bquote('&&index_start_irm&i'),
>       %bquote('&&index_base_period&i') ,
>       %bquote('&&official_pub_title&i')
>       )
>  ) by oracle;
>       %end;
>
>    disconnect from oracle ;
>     quit;
>
>     option mprint;
>     %if &sysdbrc = 0 %then
>      %do;
>     %put;
>     %put New &count_add PPI_INDEX_SID(s), and other fields are
> added to PPI_INDEX;
>  %put;
>     %let return_status = S;
>      %end;
>     %else
>      %do;
>      %put;
>         %put ERROR: Failed to insert new index code into PPI_INDEX table;
>
>   %put;
>         %let return_status = F;
>      %end;
>     %end;
>  %else
>    %do;
>    %put No new index code to be added to PPI_INDEX;
>    %end;
> %mend inspix;
>