topic Re: Using loop to aggregate variables from different tables in PROC SQL in SAS Programming

Using loop to aggregate variables from different tables in PROC SQL

MWicks69420 — Thu, 26 Jul 2018 16:09:41 GMT

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!

Re: Using loop to aggregate variables from different tables in PROC SQL

Kurt_Bremser — Thu, 26 Jul 2018 16:20:16 GMT

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

data want;
set zzz:;
run;

Re: Using loop to aggregate variables from different tables in PROC SQL

PeterClemmensen — Thu, 26 Jul 2018 16:22:55 GMT

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

Re: Using loop to aggregate variables from different tables in PROC SQL

Reeza — Thu, 26 Jul 2018 16:30:56 GMT

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;

Re: Using loop to aggregate variables from different tables in PROC SQL

MWicks69420 — Thu, 26 Jul 2018 17:08:36 GMT

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?

Re: Using loop to aggregate variables from different tables in PROC SQL

MWicks69420 — Thu, 26 Jul 2018 17:20:24 GMT

EDIT: MERGE ZZZ: ; works splendidly!

Re: Using loop to aggregate variables from different tables in PROC SQL

Reeza — Thu, 26 Jul 2018 17:19:37 GMT

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;