BookmarkSubscribeRSS Feed
Tanmoy
Calcite | Level 5

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.

6 REPLIES 6
LinusH
Tourmaline | Level 20

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
RB1Kenobi
Quartz | Level 8

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
Calcite | Level 5

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
Super User

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
Quartz | Level 8

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
Calcite | Level 5

Thanks guys!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3014 views
  • 6 likes
  • 4 in conversation