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.