SAS Viya metadata query

[BerryH]

Hello,

Did I understand correctly that the 'traditional' metadata data step queries are no longer working in SAS Viya? Are there any alternatives to get the same functionality?

Thanks,

Berry

[SASKiwi]

Correct. There is no SAS 9 - style metadata in Viya. What type of metadata did you have in mind?

[BerryH]

Ok, thanks.

We now use for example metadata queries to extract information about extended attributes for tables, information about which users are in which groups, information about defined libraries and configured options, etc.

[SASKiwi]

What about using the SAS DICTIONARY tables for info on libraries and tables? As I understand it there is a SAS V9 workspace server in Viya which should support these. Unfortunately I don't have access to Viya to test this.

[BerryH]

Perhaps this is possible. However, in my experience the data step metadata queries are able to retrieve more metadata information than the dictionary tables.

[gwootton · Accepted solution]

If you want to pull information programmatically from Viya you can use the sas-admin CLI or REST APIs. I think I've put a few programs out there to get information on users and groups already from the identities microservice.

https://communities.sas.com/t5/Administration-and-Deployment/SAS-VIYA-Fetching-List-of-Users-Groups-...
https://communities.sas.com/t5/Administration-and-Deployment/Metadata-scripts-for-SAS-Viya/td-p/6687...

For library and table data you could do something similar from the CAS administration microservices or CAS directly.

Using SAS Administration Command-Line Utility:
https://go.documentation.sas.com/?cdcId=calcdc&cdcVersion=3.5&docsetId=calcli&docsetTarget=n01xwtcat...

Using REST APIs to get library/table info:
https://developer.sas.com/apis/rest/v3.5/DataManagement/#data-sources
https://developer.sas.com/apis/rest/v3.5/DataManagement/#data-tables

[BerryH]

Thanks Greg, very helpful.

[MarkBodt_NZ]

I read in your post that you're after metadata and I think: "extract information about extended attributes for tables".

You can use the CLI and REST APIs mentioned in the answer in this post.

Just recently I had to pull out 'metadata' for some CAS tables, specifically the columns, type and lengths. I needed this information to help me code a CAS Star Schema view.

I did this using SAS9 code:

You can get this CAS table information a couple of ways in SAS 9 code:

1) using Proc CASUtil, with the contents statement https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/casref/p0e32z0e8q5ge6n1fk1u8cuhzj4g.htm

This returns output similar to the proc contents;

2) Using Proc CAS with the table.tabledetails actionset. Note that this action set may not be available in earlier versions of 9.4M5

You can wrap your proc code with ods statements to get the output into datasets like this:

%let table=FACT_XXX;

/*ods trace on;*/

ods output  TableInfo=TableInfo;

ods output  TableDetails=TableDetails;

ods output  ColumnInfo=ColumnInfo;

proc casutil;

      contents incaslib="dev_AAAA" casdata="&table";

quit;

ods trace off;

I could then post process the ColumnInfo dataset and used that to generate code for creating a star schema view

Mark