SAS Macro loop that ends based on criteria

I’m writing in SAS & primarily use proc sql for data cleaning.

I have a bit of code where I sequentially pull out records (pins) from a table . I want the code to loop until the table is depleted. I wrote out this macro called catalina. I want it to run until the count variable I make on the final line of the macro is = to 0.

%macro catalina;   proc sql;   create table todelete as select max(pin_todelete) as pin_todelete from pins group by pin_todelete;   delete from pairs where pin_a = any(select pin_todelete from todelete);   delete from pairs where pin_b = any(select pin_todelete from todelete);   insert into matched select * from pairs where match_dist = (select min(match_dist) from pairs);   insert into pins (pin_todelete) select pin_a from matched;   insert into pins (pin_todelete) select pin_b from matched;   select count(*) as count from pairs;   %mend;   

pin is my unique lookup value.
My table houses the pins I want to sequentially remove from my parent table until that table is down to 0.

Thanks for the help!

Add Comment
1 Answer(s)

To have the macro generate multiple blocks of code you need some macro logic. Looks like you need a simple %DO %UNTIL() construct. Note that you will need to create an actual macro variable in the last step and not just print the result to the output destination like your current code. Are you positive your process will always get to zero obs? If not then add more logic to stop after some fixed number of steps. Or perhaps stop based on some other criteria that will always occur, like zero observations detected to be removed.

So put the parts that don’t repeat before or after the %DO loop.

%macro catalina; %local count ; %let count=-1; proc sql; %do %until(&count <= 0);   create table todelete as      select max(pin_todelete) as pin_todelete from pins group by pin_todelete   ;   delete from pairs where pin_a = any(select pin_todelete from todelete);   delete from pairs where pin_b = any(select pin_todelete from todelete);   insert into matched       select * from pairs       where match_dist = (select min(match_dist) from pairs)   ;   insert into pins (pin_todelete) select pin_a from matched;   insert into pins (pin_todelete) select pin_b from matched;   select count(*) format=32. into :count trimmed from pairs; %end; quit; %mend; 

If you explain more about what the algorithm is doing you might get an answer that shows an easier way to do it.

Answered on August 30, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.