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
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.