SAS: How to loop though work file to append multiple database tables

Question

I have a large list of database tables that I need to append into one amalgamated table on SAS.

• This table list changes regularly.
• Some tables in this list don't have don't use the same column names.
• Where the column name is different, the list will indicate what the equivalent name is.

This table list is imported into SAS from a csv file and resembles the below data:

index	table_name	column_1_name
1	table_one_a	column1
2	table_one_b	columnOne
3	table_one_c	column_1
4	table_one_d	column_1_1
etc ........	etc.....	etc.....

I want to append every table in this list, and then change the names where applicable by referencing the column_1_name column in the above list.

The below code was adapted from this link and is an illustration of how I want SAS to append the tables together from the above list. However I don't know how to convert the above list of tables with their column names into variables so they can be looped through in the below illustrated macro.

Is there a way that I can convert this list of tables into a variable that I can then looped through by its index number?

Any help is much appreciated.

 libname dbname ODBC DSN=databaseName;

 %let table = table_one_a; run;

 %let column_one = column1; run;

 %MACRO append_tables;

     %If index =1 %Then %Do;

     data first_table;
     set dbname.&table.;
     &column1. = column1; 
     keep column1 column2  etc;
     run;

     %End;

     %Else %Do;

     data later_table;
     set dbname.&table.;
     &column1. = column1; 
     keep column1 column2  etc;
     run;

     proc append 
     BASE = first_table
     DATA = later_table;
     run;

     %End;

 %MEND;

Answer 1

Instead of using a loop, why don't you just extract all the tables name from the list? Also, why are you not using a single SET statement instead of the APPEND procedure?

*-- Create table list sample --*;

data csv_list;
length column_name $20.;
table_name = "table_one_a"; column_name = "Col1"; output;
table_name = "table_one_b"; column_name = "Column1"; output;
table_name = "table_one_c"; column_name = "Colonne1";output;
run;

*-- Create synthetic data for each table in table list --*

data temp.table_one_a;  
Col1 = 1;
run;

data temp.table_one_b;
Column1 = 2;
run;

data temp.table_one_c;
Colonne1 = 3;
run;

libname temp "/home/kermit/stackoverflow";

*-- Create macro tables with all table names + renaming --*

proc sql;
  select cats(strip("temp."||table_name),"(rename=(",column_name,"=column1))") into :tables separated by " "
    from csv_list;
quit;

*-- Append using set statement --*

data want;
  set &tables.;
run;

*-- Tables macro is a concatenation of all table names in table list, separated by a space --*

%put &=tables;
TABLES=temp.table_one_a(rename=(Col1=column1)) temp.table_one_b(rename=(Column1=column1)) 
 temp.table_one_c(rename=(Colonne1=column1))


*-- Result is the vertical combination of all tables + renaming in the tables macro --*

data _null_;
set want;
put id;
run;

column1
   1   <-- table_one_a
   2   <-- table_one_b
   3   <-- table_one_c