How to create multiple macro variables using a loop in SAS Enterprise Guide?

I am constantly using SAS datasets in SAS EG to create macro variables that can be used as variables in a query from SAS EG to my internal servers. There is a character limit of 65,534 for a macro variable. When I need to get 100k ids that are 9 to 15 digits in length the number of macro variables required to create really adds up. I am asking the community if there is a way to create a large number of macro variables with a loop instead of doing it manually.

For instance the manual way to create these macro variables would be something like the following:

    proc sql; create table alerts as select distinct review_id format best12. from q4_21_alerts order by review_id;quit;
    proc sql; create table alerts1 as select review_id, monotonic() as number from alerts order by number; quit;

    proc sql; select distinct review_id into:alert_ids1 separated by ',' from alerts1 where number between 1 and 7000; quit;

    proc sql; select distinct review_id into:alert_ids2 separated by ',' from alerts1 where number between 7001 and 14000; quit;

    proc sql; select distinct review_id into:alert_ids3 separated by ',' from alerts1 where number between 14001 and 21000; quit;

    proc sql; select distinct review_id into:alert_ids4 separated by ',' from alerts1 where number between 21001 and 27000; quit;
    .
    .
    .
    proc sql; select distinct review_id into:alert_ids21 separated by ',' from alerts1 where number between 140001 and 147000; quit;

I am hoping to find a way to do something like the following:

N = 145417 
#total number of review_ids that need to be contained in SAS macro variables
L = 8 
#length/number of characters/digits in each review_id
L = L + 1
#length/number of characters/digits in each review_id with 1 added for the comma separation in macro variable
stop = N*L

i = 1
while(i<=stop){
 some code to create all 21 macro variables 
}

then be left with macro variables alert_ids1, alert_ids2,...,alert_ids21 that would contain all 145,417 ids i need to then use in a query for my internal servers.

Any help would be appreciated!

I've used google and sas communities and have code to do this process manually...

I am unsure what your final query is and would advise building a SQL query that specifically filters to the IDs you want. e.g.:

proc sql;
    create table want as
        select * 
        from have
        where id in(select id from id_table)
    ;
quit;

But if you need to have a comma-separated list of macro variables that abides by the 65,534 character length, the safest way is to create one ID per macro variable. You can very easily do this with a data step.

data _null_;
    set alerts1;
    
    call symputx(cats('alert_id', _N_), review_id); 
    call symputx('n_ids', _N_);
run;

This will create the macro variables:

alert_id1
alert_id2
alert_id3 
...

Now you need to create a loop that makes these all comma-separated.

%macro id_loop;
    %do i = 1 %to &n_ids;
&&alert_id&i %if(&i < &n_ids) %then %do;,%end;
    %end;
%mend;

Note the code format is a bit strange to keep the output formatted correctly. Now run this macro and you'll see a comma-separated list of every alert ID:

%put %id_loop;

id1, id2, id3, ...

You can put this in a query, such as where alert_id in (%id_loop). Keep in mind that doing this will load up the symbol table with a ton of macro variables. This is not the recommended way to query, but it is one way to achieve what you asked.