Table not appending properly

[spg]

I’m trying to create a table by

• First, running proc expand on a variable (100+) and
• Second, append these newly created variables on to one single table.

However, when I use this code below, it keeps replacing the newest variable instead of adding them . .. So instead of a table with 100+ variables, I end up with a table with just a couple. Where am I going wrong?

%macro test;

%do i=1 %to 110;

data got&i;

set have (keep=date var&i);

run;

proc expand data= got&i out=want&i;

convert var&i = new_var&i /transformout=(movave 4);

run;

proc sql;

create table final as

select a.*, b. new_var&i

from semifinal a

left join want&i b on a.date=b.date;

quit;

%end;

%mend;

%testmacro;

[ballardw · Accepted solution]

I don't know what is in you table Semifinal but you keep replacing the table Final with the contents of Semifinal and one variable.

I expect that you only have the 110th variable in last version of Final. If I understand what you may be attempting you need to do something after each "create table final" to make it the table semifinal. Maybe:

proc sql;

create table final as

select a.*, b. new_var&i

from semifinal a

left join want&i b on a.date=b.date;

Create table semifinal as

select * from final;

quit;

[spg]

Thank you ballardw! That did the trick.