PROC SQL more then two macro values doesnt get resolved/passed correctly with quotation marks

[Chados]

Hello,

i dont know how to proceed on this.

data work.test;

length result $100 conv $100;

result = "";

conv = "";

run;

%macro run;

/* here i build some dynamic sql*/

%do i=1 %to 10;

%let query = Select %scan(&name.,&i) as  %scan(&name.,&i) from maps.%scan(&table.,&i) where color = 'blue';

%let tmp_name = %scan(&name,&i.);

/*and now the strange part*/

proc sql outobs=1;

insert into work.test (result,conv) values ("&query", "tmp_name");
/* so this work and i got as the result

result                                                                                                        conv

SELECT COMMENT1 from maps.XY where color ...                   COMMENT1

SELECT Dell from maps.XY where and so on                               DELL

But i want the result from the dynamic sql, like:

result                        conv

Santa Barb               COMMENT1

Sakassuro                  DELL

With

proc sql outobs=1;

insert into work.test (result)  (&query);
i get the correct data:

result                     

Santa Barb          

Sakassuro                 

But if i want to add some more columns, i need to add the quotation marks in combination with values and i cant get by that.
I have tried many options, even with a new dataset and so on, but i cant get it solved.

[Tom]

You are using the wrong SQL code.  VALUES() wants actual VALUES, not code.

Also make sure to tell %SCAN() what character(s) it should use a the delimiter in your list of names.  Otherwise your code will have issues when you use VALIDVARNAME=ANY and the names include some of the other delimiter characters that %SCAN() uses by default when you don't specify.

Either run the SQL code and store the result into a macro variable so you can use it to generate the VALUES () clause.

%let tmp_name = %scan(&name,&i,%str( ));
%let tmp_table = maps.%scan(&table,&i,%str( ));
proc sql noprint;
select quote(trim(&tmp_name),"'")
  into :tmp_result 
  from &tmp_table
  where color='blue'
;
insert into work.test (result,conv) values (&tmp_result, "&tmp_name");
quit; 

Or just use SELECT instead of VALUES.

proc sql noprint;
insert into work.test (result,conv) 
select &tmp_name,"&tmp_name"
  from &tmp_table
  where color='blue'
;
quit; 

[Chados]

thank you for your answer, but i dont want no other sql generated (and the query works), because there is some more logic behind that (to exclude this and that).

So query has the whole sql i need and with
%let query = Select %scan(&name.,&i) as %scan(&name.,&i) into :tmp_result from maps.%scan(&table.,&i) where color = 'blue'; i got the warning tmp_results doenst get resolved.

[Tom]

Please re-read my previous post.

Once you made that macro variable you called QUERY did you actually as SAS to run it?  It not there is no way that SAS would know to put anything into the macro variable TMP_RESULT.

Please show code that works WITHOUT ANY MACRO VARIABLES.

Another thing to remember to do that I did not show in my code is to make sure that the macro variable gets created.  Which might not happen  if no observations satisfy the WHERE clause of the query.  The easiest way is to assign some default value before running the query.

%let query = 
select %scan(&name.,&i) as %scan(&name.,&i) into :tmp_result 
from maps.%scan(&table.,&i) 
where color = 'blue'
; 

proc sql noprint;
%let tmp_result= ;
&query;
quit;

[ballardw]

A couple of coding style comments:

%macro run;

Since the call to this macro would be %run it is very easy to confuse with the keyword "run" that ends most procedures. More descriptive names are a very good idea.

And more serious in terms of debugging or preventing problems

%let query = Select %scan(&name.,&i) as  %scan(&name.,&i) from maps.%scan(&table.,&i) where color = 'blue';

This one line uses two macro variables &name and &table without any idea of where they may be set. Just having macro variables appear that are not created in the macro or passed as parameters is a common cause of "that was working yesterday, why isn't it today?". And absolute bare minimum would be to have a comment as to where these macro variables are create. Better is to have them as actual parameters in the definition and call.

[Kurt_Bremser]

So you want to create code dynamically.

First, post a non-macro example of the code you want to run for a single instance.

Next, post the same code for another single instance, so we can see what changes between them.

Third, post the data you have from which you want to develop the dynamic elements of that code.