All_Tables or All_Columns

[Tanmoy]

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.

[LinusH]

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

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

[RB1Kenobi]

Hi

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;

[Tanmoy]

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..:)

[Ksharp]

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

[RB1Kenobi]

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#p1o13d7wb2zfcnn19...

[Tanmoy]

Thanks guys!!