topic Re: How do I subset a library so that it no longer contains views and just tables? in SAS Programming

How do I subset a library so that it no longer contains views and just tables?

kyle234 — Thu, 02 May 2024 15:22:37 GMT

I have a library and I want just the tables and not the views in the library. Thank you!

How do I create a new library from a library on a where statement?

kyle234 — Thu, 02 May 2024 13:33:57 GMT

I have a library that I want to subset to only include tables that are dbms_memtype='table'. Thank you!

Re: How do I subset a library so that it no longer contains views and just tables?

maguiremq — Thu, 02 May 2024 15:29:30 GMT

I've never done it, but documentation from PROC DATASETS appears you can set MEMTYPE to view. I don't have time to test it either, so someone else may know better.

Next question would be what you want to do with it. Delete? Move?

Re: How do I subset a library so that it no longer contains views and just tables?

Tom — Thu, 02 May 2024 16:19:54 GMT

@kyle234 wrote:

I have a library and I want just the tables and not the views in the library. Thank you!

What do you mean by "want"?

Do you want to make a list of the table names?

Do you want to delete the views?

Do you want the views to somehow disappear from the GUI list of tables but not actually be deleted? Why? What GUI tool are you using? SAS/Studio? Something else?

Is the libref pointing at actual SAS datasets and SAS views? You can check DICTIONARY.MEMBERS to tell what type of file each member in the library is.

Or is the libref pointing at an external database like Oracle? If the later then how you can tell if a "table" in the library is actually a view would probably depend on what database you are using.

Re: How do I subset a library so that it no longer contains views and just tables?

kyle234 — Thu, 02 May 2024 16:37:24 GMT

There is a library is SAS currently with table want the library to only have view tables. Not sure if that means copy into new library. Thanks

Re: How do I subset a library so that it no longer contains views and just tables?

Reeza — Thu, 02 May 2024 16:38:19 GMT

As far as I know you cannot limit a library to just views or tables.

Re: How do I subset a library so that it no longer contains views and just tables?

kyle234 — Thu, 02 May 2024 16:40:32 GMT

I would like dbms_memtype not the memtype data. I would just like the library changed to just include those tables. Thank you!

Re: How do I subset a library so that it no longer contains views and just tables?

Quentin — Thu, 02 May 2024 17:55:51 GMT

A SAS libref points to a location that stores stuff (tables, views, catalogs, etc.) That location could be a folder on Windows, a directory on linux, a database schema, or many other types of locations.

So if your libref is pointing to, say, a directory, and you have both tables and views in that directory, then the library will have tables and views in it. If you don't want views to be in the library, you would need to move them from that location.

That said, your goal isn't really clear to me. It might help if you describe your current situation. What OS are you using? Where are the tables and views stored? How did you create the libref that points to the library (can you show the LIBNAME statement)? Why don't you want the views to be in the library?

Re: How do I subset a library so that it no longer contains views and just tables?

Reeza — Thu, 02 May 2024 18:01:50 GMT

Workaround, create a new library that has only views that points to only tables in the library. Run it every x hours to have it updated (data will not update, only view definitions).

Re: How do I subset a library so that it no longer contains views and just tables?

Tom — Thu, 02 May 2024 18:30:33 GMT

@kyle234 wrote:

I would like dbms_memtype not the memtype data. I would just like the library changed to just include those tables. Thank you!

What is the DBMS? You might try adding the DB_OBJECTS=TABLES option to your LIBNAME statement that defines the "library".

DB_OBJECTS= LIBNAME Option

DB_OBJECTS=TABLES | VIEWS | SYNONYMS | PUBLIC_SYNONYMS | ALL

Specifies which database objects to return with PROC DATASETS or in SAS Explorer.

Product: SAS/ACCESS for Relational Databases

Document: SAS/ACCESS for Relational Databases: Reference

Re: How do I create a new library from a library on a where statement?

Ksharp — Fri, 03 May 2024 05:21:06 GMT

proc copy in=in out=out memtype=data;
run;

could only copy sas datasets from IN libname to OUT libname.