CODEWITHSUNDEEP
sqlloopssassas-macro
kroach
kroach

Reputation: 25

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 (ids) 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(id_todelete) as id_todelete from ids group by id_todelete;  
delete from pairs where id_a = any(select id_todelete from todelete);  
delete from pairs where id_b = any(select id_todelete from todelete);  
insert into matched select * from pairs where match_dist = (select min(match_dist) from pairs);  
insert into ids (id_todelete) select id_a from matched;  
insert into ids (id_todelete) select id_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!

Upvotes: 1

Views: 525

Answers (1)

Tom
Tom

Reputation: 51566

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.

Upvotes: 1

Related Questions