topic Re: Query values accross table and column name in SAS Programming

Query values accross table and column name

BL_463_IDR — Mon, 22 Nov 2021 03:05:09 GMT

Dear All

I'm trying to find/search values from across tables or columns name based on value name that i want to search. So far, i could search and show the tables or columns by table or column name.

This is my script that i used to search and show the tables or columns by table or column name:

PROC SQL;
CONNECT TO ORACLE (USER="xxxx" PASSWORD="xxxx"
PATH="(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = xxx)
(HOST = xxxxxxx) (PORT = xxxxx)))(CONNECT_DATA =(xxx = xxxx )))");
CREATE TABLE CEK.CEK_DWH_COLUMN (COMPRESS=YES) AS
SELECT * FROM CONNECTION TO ORACLE
(
SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%PRODUCT%');
QUIT;

From that query, the result in .bdat like example below:
OWNER | TABLE_NAME | COLUMN_NAME

DWH PROUDUCT PROGRAM

DWH PROUDUCT SRRKID

etc...

the result that i want, are like this below:

OWNER | TABLE_NAME | COLUMN_NAME | VALUES_NAME

DWH PROUDUCT PROGRAM TITANIUM

DWH PROUDUCT SRRKID 32102

etc...

Can anyone help me, how to find/search values from across tables or columns name based on values name?, thanks for your attention.

Regards

Re: Query values accross table and column name

Kurt_Bremser — Mon, 22 Nov 2021 06:21:31 GMT

From where do you get the values for this fourth variable?

Re: Query values accross table and column name

BL_463_IDR — Tue, 23 Nov 2021 08:08:56 GMT

i want to get the values from accross the table_name and column_name

Re: Query values accross table and column name

Kurt_Bremser — Tue, 23 Nov 2021 08:26:10 GMT

This does not answer my question. The values are not present in your example data, so from where do you get them?

See Maxim 42. Posting a detailed question will get you answers beyond "42".