Using loop to aggregate variables from different tables in PROC SQL

[MWicks69420]

My situation is I have several different tables that were produced from a macro function that I now want to bring together into one table. The number of tables (lets call the number i) can vary depending on what is run through the program. Lets say my i tables are ZZZ&i and each one has a column result&i, I want to make one dataset where if ZZZ&i exists then result&i is added as a column. I've tried solving this with a %DO loop in PROC SQL but it doesn't seem to accept my code unless the loop is outside my "CREATE TABLE" statement.

An example of a successful output would be tables ZZZ1, ZZZ3, and ZZZ4 exist and one final table is created with variables result1, result3, and result4 each from their respective origins. 

Thanks in advance for any help!

[Kurt_Bremser]

If it's really like that, the solution is extremely simple:

data want;
set zzz:;
run;

[MWicks69420]

Thanks! Only problem is it creates a union rather than a join--stuff is stacked vertically creating lots of NULLs, is there any way around this?

[MWicks69420]

EDIT: MERGE ZZZ: ; works splendidly! 

[Reeza · Accepted solution]

That's a merge and I think its the same concept, just MERGE instead of SET. Make sure the variable names are unique in each otherwise you can't use this approach because you'll have to rename the variables.

data want;
merge class: ;
by linked_variable;
run;

[PeterClemmensen]

Show us what code you have so far? And show us what your data looks like?

[Reeza]

You can shortcut list data set names as well. 

Use : for prefixes or you can use - as well.

Either option below will work most likely. It depends on you having a naming convention so I usually use something like _results to ensure that no other data sets will be included accidentally.

set zzz: ;
set result: ;

set result1-result4;