Problem of single quotes in a PROC SQL inside a macro program

[raf64flo]

Hello,

I have a problem with singles quotes in a PROC SQL inside a macro program. I have a macro-variable named matrix_state_name__1 which contains value : plats cuisines
and I want send this value in a Oracle base via PROC SQL.
&column_tmp.__&j_ is returned by another macro and it contains, in this example, the value :
matrix_state_name__1

var is the variable to be inserted in Oracle table, it contains well the value between quotes which is transmitted to proc sql, but a run-time, the log returns an error.

I have SAS Enterprise Guide 4.1 (4.1.0.500) with SAS V9.

Here is the code that permit you to test the problem :


____________________________________________
OPTIONS MLOGIC MPRINT SYMBOLGEN;

/*Definition and initialization of variables*/
%let matrix_state_name__1 = plats cuisines;
%let column_tmp = matrix_state_name;
%let j_ = 1; /* it is a value used for a %do loop*/
%let var = &column_tmp.__&j_;
%put var is : **&var**;
%put value of var : **&&&var**;
%let var1 = %str(%'&&&var%');
%put value of var1 : **&var1**;

/* Creation of the table in which we want insert data*/
proc sql;
create table matrix_state (id_state num(10), state_name char(20));
quit;

/* This macro does not function because of single quotes are not well transmitted (cf log) */
%macro insert1;
proc sql;
insert into work.matrix_state (id_state, state_name) values (2, &var1);
quit;
%mend;

%insert1;

/* In this testing proc sql, the insertion works well, because the value to be inserted is defined and between quotes in the sql request itself*/
proc sql;
insert into work.matrix_state (id_state, state_name) values (6, 'plat cuisine');
quit;
____________________________________________



Thanks for your help.

[darrylovia]

What is odd if you look in the global symbol table the macro variables that you created do not have single quotes.

data macrovars;
set sashelp.vmacro;
if upcase(name)=:'VAR';
run;

*********

I tried this and it worked you may have to change you variable initialization to conform to your program

data _null_;
n=1;
var='plats cuisines 2';
call symput('var'||strip(put(n,8.)),"'"||strip(var)||"'");
run;

[Michelle_sas]

I'm not sure why you have the %str in the %let statement, but if you keep that then you need to change your insert statement to:

insert into work.matrix_state (id_state, state_name) values (2, %unquote(&var1));

and that should work for you.
Michelle