I have an example like this:
proc sql;
select dealno into :deal_no
from deal_table;
Now I want to traverse the variable deal_no now containing all dealno in table deal_table but I don't know how to do it.
-
2Please tell us what you're trying to do with this. While there are certainly some valid reasons for traversing a macro variable list, nearly all of the time there is a much better solution using existing data step programming methods.Joe– Joe2012-11-13 15:11:25 +00:00Commented Nov 13, 2012 at 15:11
-
I want to take a list of item and then inspect each item if they satisfy a specified condition.For example: %macro loop; ---use loop here to retrieve items in list --- insert into table my_table select * from your_table where deal_id not in (item) %mend;trisas– trisas2012-11-14 01:50:22 +00:00Commented Nov 14, 2012 at 1:50
-
If that's what you want to do, then do it in SQL, or in a data step, without the macro variable loop. Looping in macro language is nearly always wrong - it is the slow, slow way to do it. If you're just doing the above once, then Keith's solution is better - gets you all of the values in one macro variable. If you are doing some sort of complicated join-insert, it can undoubtedly be done either in SQL or a data step without macros.Joe– Joe2012-11-14 03:33:56 +00:00Commented Nov 14, 2012 at 3:33
-
Thank for advice. I'm just starting with SAS so I don't know much about it. You quoted "do it in SQL, or in a data step, without the macro variable loop", so may i see your solutions also ? I want to know solutions from SAS experienced userstrisas– trisas2012-11-14 09:27:35 +00:00Commented Nov 14, 2012 at 9:27
-
I'm using the approach descibed in my answer when I have a few distinct values to iterate over (10 or so), and a large amout of work to do for each of those values. I usualy have a macro that does an analisys for a given month, and If I have to do that analisys for a list of months that I have to calculate from some dataset, I use that approach.Dejan Peretin– Dejan Peretin2012-11-14 10:25:44 +00:00Commented Nov 14, 2012 at 10:25
|
Show 1 more comment
3 Answers
Another option is add 'separated by' to the sql code, which will add a delimiter to the values. You can then use the SCAN function in a data step or %SCAN in a macro to loop through the values and perform whatever task you want. Example below.
proc sql noprint;
select age into :age separated by ','
from sashelp.class;
quit;
%put &age.;
data test;
do i=1 by 1 while(scan("&age.",i) ne '');
age=scan("&age.",i);
output;
end;
drop i;
run;
Comments
If you do
%put &deal_no;
you can see that it only contains the first value of dealno, not all of them.
To avoid that you can do something like this:
proc sql;
create table counter as select dealno from deal_table;
select dealno into :deal_no_1 - :deal_no_&sqlobs
from deal_table;
quit;
%let N = &sqlobs;
%macro loop;
%do i = 1 %to &N;
%put &&deal_no_&i;
%end;
%mend;
%loop; run;
Comments
Here's another solution.
proc sql noprint;
select age into :ageVals separated by ' '
from ageData;
quit;
%put &ageVals;
%macro loopAgeVals; %let i = 1; %let ageVal = %scan(&ageVals, &i);
%do %while("&ageVal" ~= "");
%put &ageVal;
%let i = %eval(&i + 1);
%let ageVal = %scan(&ageVals, &i);
%end;
%mend;
%loopAgeVals;
Comments
