topic Re: All_Tables or All_Columns in SAS Enterprise Guide

All_Tables or All_Columns

Tanmoy — Wed, 14 Jan 2015 11:16:46 GMT

Hi,

How to find all the sas tables or library or other metadata information?. In Oracle we have tables like All_Tables or All_Tab_columns and many others.I checked with dictionary table present in sas like vtable or vslib but I couldn't find all the library or tables except some sas predefined metadata.

Pls help.

Re: All_Tables or All_Columns

LinusH — Wed, 14 Jan 2015 11:24:56 GMT

In PROC SQL, access the DISCTIONARY.-tabels. See PROC SQL doc for details.

For other tools, there are corresponding VIEWs in SASHELP library.

Re: All_Tables or All_Columns

RB1Kenobi — Wed, 14 Jan 2015 12:05:02 GMT

I hope I've understood your question correctly.

Option 1 - see the contents of a defined library:

proc sql;

create table Library_content

as select

memname as Dataset_Name

, *

from Dictionary.tables

where LIBNAME='YOUR LIBNAME HERE'

and MEMTYPE='DATA'

;quit;

Option 2;

Try Proc Contents to explore individual tables.

Proc contents data=libname.tablename;

run;

Re: All_Tables or All_Columns

Tanmoy — Wed, 14 Jan 2015 13:05:31 GMT

Thanks Linus and RB1Kenobi for your answer but my case is diff...by firing the query mentioned by Kenobi, I can get the table content. But that's not what I need. I am building an automatic code where if you know or don't know the table name. If you give like statement ,it will fetch the table name and it will be used as a source table for the target. In oracle it could be somewhat like this.....

select table_name from all_tables where table_name like '%emp%'

-----output : 5 row with table name have been received.

loop 1..max_count(in this case it is 5)

insert into target_table select a.col1,a.col2 from &table_name

----- the loop will be executed 5 times and for 5 diff table data will be inserted in the target table....

hope I have illustrated my situation vividly..:)

Re: All_Tables or All_Columns

Ksharp — Wed, 14 Jan 2015 13:40:42 GMT

use call execute()

data _null_;
 set sashelp.vmember(where=(libname='SASHELP' and memname like '%CLASS%' and  MEMTYPE='DATA')) end=last;
 if _n_ eq 1 then call execute('proc sql;');
 call execute('insert into target_table select * from '||cats(libname,'.',memname)||';');
 if last then call execute('quit;');
run;

Xia Keshan

Re: All_Tables or All_Columns

RB1Kenobi — Wed, 14 Jan 2015 13:45:36 GMT

I'm afraid I don't have an oracle background, but here's a suggestion by using a macro loop approach.

* Define a macro to loop for your filters through;

%macro tablexctract (tbl_filter=);

* Open the Library and keep the table names that meet the "WHERE MEMNAME=" criteria;

proc sql;

create table Library_content

as select

memname as Dataset_Name

, *

from Dictionary.tables

where LIBNAME='your libname here'

and MEMTYPE='DATA'

and memname like "%&tbl_filter.%" /** Additional "where" condition ***/

;quit;

/* Put the results into a macro variable. Caution - this approach relies on a single table name

being returned from the filter above */

data _null_;

set work.library_contentmaps (obs=1);

call symputx('tblname',dataset_name);

run;

Proc sql;

create table NEW_table_&tbl_filter.

as select

from &tblname.

;quit;

%mend;

* Use the macro parameter TBL_FILTER to define the table name content you woudl like to extract;

%tablexctract(tbl_filter=%str(emp); * loop 1;

%tablexctract(tbl_filter=%str(...); * loop 2;

%tablexctract(tbl_filter=%str(...); * loop 3;

%tablexctract(tbl_filter=%str(...); * loop 4;

%tablexctract(tbl_filter=%str(...); * loop 5;

A very different approach would require you to research the autocall macro functionality, see %sysfunc examples 4 & 5 on this link:

http://support.sas.com/documentation/cdl/en/mcrolref/62978/HTML/default/viewer.htm#p1o13d7wb2zfcnn19s5ssl2zdxvi.htm

Re: All_Tables or All_Columns

Tanmoy — Wed, 14 Jan 2015 13:58:20 GMT

Thanks guys!!