topic Re: SAS Viya metadata query in SAS Viya

SAS Viya metadata query

BerryH — Thu, 11 Mar 2021 13:35:51 GMT

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

Re: SAS Viya metadata query

SASKiwi — Thu, 11 Mar 2021 19:11:44 GMT

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

Re: SAS Viya metadata query

BerryH — Fri, 12 Mar 2021 08:06:47 GMT

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.

Re: SAS Viya metadata query

SASKiwi — Fri, 12 Mar 2021 21:39:37 GMT

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.

Re: SAS Viya metadata query

BerryH — Mon, 05 Apr 2021 13:17:30 GMT

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

Re: SAS Viya metadata query

gwootton — Mon, 05 Apr 2021 14:32:28 GMT

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-Creation-Date/td-p/723793
https://communities.sas.com/t5/Administration-and-Deployment/Metadata-scripts-for-SAS-Viya/td-p/668751

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=n01xwtcatlinzrn1gztsglukb34a.htm&locale=en

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

Re: SAS Viya metadata query

BerryH — Mon, 05 Apr 2021 14:56:41 GMT

Thanks Greg, very helpful.

Re: SAS Viya metadata query

MarkBodt_NZ — Tue, 20 Apr 2021 06:32:23 GMT

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