How to loop through a macro variable in SAS

Question

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.

Answer 1

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;

Answer 2

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;