How to fetch data from multiple RDBMS tables and get it loaded to one common SAS table.

[Scott177]

Hi Everyone,

I have a requirement to fetch data from multiple tables based on a name pattern from Oracle DB and get it loaded to SAS table. I know that we can get the exact table names by applying pattern name filter in the Oracle system table ALL_TABLES and get the TABLE_NAME value. But i don't know how to use this information in PROC SQL step to get the desired output.

Example:

Table Names:

Employee_20200101

Employee_20200109

Employee_20200114

Employee_20200202

Employee_20200209

:

:

:

Goes on

Each tables has incremental records along with the employee details which gets updated in the respective data feed. So i need to find the list of unique Employee ID's from all the tables which starts with "Employee_" and this calculation has to go on every week when there's a new data feed coming in.

Ex: Table 1(first week), Table 1 + Table 2(second week), Table 1 + Table 2 + Table 3 (Third week)

I thought of storing the table names in a macro variable ,but i don't know how to use the values from Macro variable inside an iterative statement.

Logic which i thought of is this.

1.Get the total count of tables and get it assigned to a macro variable

 Ex: count=5

2. Find the table names and get it assigned to another macro variable

Ex: tablist

3.Include the count in the looping statement to execute the PROC SQL iteratively

4.Inside the PROC SQL give the macro variable tablist to execute it over and over again for all the tables and get the data loaded to SAS table

I'm very new to SAS and i'm finding it bit hard to translate the logic into SAS code. Could someone help me out on this? Please let me know if there are any mistakes in my requirement. I can correct and learn from the suggestions. Thanks and Have a great day

Regards,
Scott

[ChrisNZ · Accepted solution]

May I suggest a much simpler approach:

libname ORALIB oracle .... ;
data WANT;
  set ORALIB.EMPLOYEE_: indsname=SRC;
run;

This will read all the tables in order.