Search code examples
sassas-macro

SAS EG how to stop a do loop after a function runs/fix results

I am currently trying to loop a coalesce function in EG using macro language. The issue I'm having is the loop is populating all empty columns, rather than just the first empty cell. I have tried a form of if logic to stop the loop but not successfully and also tried and failed to fix the results after by comparing if the adjacent columns are the same and then deleting. I would like the loop to only populate the first empty cell as I need to run a 2nd coalesce loop with a 2nd variable afterwards. The attached image shows what results I am getting and what I am aiming for.

My current working code is as follows:

%macro coal;
data want;
set have;
%do i = 1 %to 5;
coal&i = coalescec(&column&i, var1);
%end;
run;
%mend;

current results

output wanted

Solution

  • An array loop, to find the first empty column, exit the loop, and populate that column with var1 :

    data want ;
  set have ;
  array c{*} column: ;
  /* loop over array and find first empty column */
  cmiss = . ;
  do i = 1 to dim(c) until (cmiss) ;
    if missing(c{i}) then cmiss = i ;
  end ;
  if cmiss then c{cmiss} = var1 ;
  drop i ;
run ;