Do Loop Deleting All But Last Entry

[jbk96]

Hi,

I've been trying to run a do loop for a proc sql statement using the below code

%macro create_tables(start,end);

%do i = &start. %to &end.;

proc sql;
create table a_bal as select
a.*,
b.a_balance%sysevalf(&i.)
from accounts a
left join cashflows b
on a.account_no = b.account_no;
quit;
%end;

%mend;

%create_tables(39,134);

But the table output only contains the first column and the last iteration of the loop i.e. a_balance134

Any idea how to get this table to have all the a_balance fields from 39-134?

Thanks

[Kurt_Bremser · Accepted solution]

You always create the same table:

create table a_bal as select

so you overwrite the result in every iteration of the macro do loop.

You seem to have a data design problem (wide dataset structure), which is solved best by using proc transpose:

proc transpose data=cashflows out=cashflows_long;
by account_no;
var a_balance39-a_balance134;
run;

Then join over account_no.