Desktop productivity for business analysts and programmers

All_Tables or All_Columns

Reply
Occasional Contributor
Posts: 10

All_Tables or All_Columns

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.

Esteemed Advisor
Posts: 5,198

Re: All_Tables or All_Columns

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

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

Data never sleeps
Contributor
Posts: 47

Re: All_Tables or All_Columns

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;

Occasional Contributor
Posts: 10

Re: All_Tables or All_Columns

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..Smiley Happy

Grand Advisor
Posts: 9,584

Re: All_Tables or All_Columns

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

Contributor
Posts: 47

Re: All_Tables or All_Columns

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

Occasional Contributor
Posts: 10

Re: All_Tables or All_Columns

Thanks guys!!

Ask a Question
Discussion stats
  • 6 replies
  • 676 views
  • 6 likes
  • 4 in conversation