How to iterate over a macro in SAS

Question

I have an example:

proc sql; select dealno into :deal_no from deal_table; 

Now I want to cross the deal_no variable, which now contains everything dealno in the deal_table, but I don’t know how to do it.

Answer 1

If you do

 %put &deal_no; 

you can see that it contains only the first dealno value, and not all of them. To avoid this, 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; 

Answer 2

Another option is to add “split” into sql code, which will add a separator to the values. Then you can use the SCAN function in the data step or% SCAN in the macro to iterate over the values ​​and perform any task you want. An example is 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 3

Here is 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;