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.
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;
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: