Hi, I found a solution that avoids using the sql join and instead uses a retain statement within the datastep. The example below is a simplied but illustrative version of the macro. In the input dataset below there are two variables to be calculated: ref1 and ref2. Their values are given by val1 and val2, respectively. Initially, their new values val1_new and val2_new are set as val1 and val2, respectively. In the macro, I wish in each row to deduct 10 from val1 and val2, and update the new values for ref1 and ref2 in subsequent rows before continuing with the loop. Note that the input dataset is not (cannot be for my purpose) sorted by neither ref1 nor ref2! Note also that ref1 = 1 is in row (1,4) while ref2 = 1 is in row (1,5), i.e unsorted. Running the macro you will be able to see in the results dataset that val1 in row 4 matches the value from val1_new in row 1, and that val2 in row 5 matches the value from val2_new in row 1. This shows that the calculations in row 1 (val1_new and val2_new) have been inserted into val1 and val2, respectively, in later rows where ref1=1 and ref2=1, respectively. data input; input id ref1 ref2 val1 val2 val1_new val2_new 3.; datalines; 1 1 1 100 100 100 100 2 2 2 200 150 200 150 3 3 3 50 50 50 50 4 1 4 100 25 100 25 5 4 1 400 100 400 100 ; run; %macro solve(); proc datasets nolist; delete results; run; proc sql noprint; select count(*) into :n from input; quit; %let i = 1; %do %while (&i. le &n); data temp; set input; if id = 1 then do; val1_new = val1 - 10; val2_new = val2 - 10; last_ref1 = ref1; last_ref2 = ref2; last_val1_new = val1_new; last_val2_new = val2_new; end; if id ge 2 then do; if ref1 = last_ref1 then val1 = last_val1_new; if ref2 = last_ref2 then val2 = last_val2_new; end; retain last_ref1 last_ref2 last_val1_new last_val2_new; run; data output (drop = last_ref1 last_ref2 last_val1_new last_val2_new); set temp (obs = 1); run; data input (drop = last_ref1 last_ref2 last_val1_new last_val2_new); set temp (where = (id ge 2)); id = _n_; run; %let i = %eval(&i.+1); proc append base = results data = output force; run; %end; %mend; %solve;
... View more