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 :
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 (18.104.22.1680) with SAS V9.
Here is the code that permit you to test the problem :
/*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*/
create table matrix_state (id_state num(10), state_name char(20));
/* This macro does not function because of single quotes are not well transmitted (cf log) */
insert into work.matrix_state (id_state, state_name) values (2, &var1);
/* 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*/
insert into work.matrix_state (id_state, state_name) values (6, 'plat cuisine');