How to get a complete metadata report?

[Kal_ETL]

Hello,

I was reading about dictionary tables, sashelp views and some functions to get metadata from data steps. However I am wondering if there is a way maybe by using Management Console or coding to get a report like this. I think it is possible only doing some joins but not sure how to match them by joining.

Library	Dataset	Group Name	Area	Username	user id	email	sas user
libfin	table 1	SAS_USERS	Financial	jhon doe	x1234567	jhondoe@corp.com	psasdemo
libit	table B	SAS_ADMINS	IT	jane doe	x7654321	janedoe@corp.com	sasadmin

If someone already did something like this, please advice in order to get a better idea.

Thank you Regards.

[SASKiwi]

What you are suggesting will only be complete if all SAS data libraries are set up in SAS metadata and all SAS tables for those libraries are also registered in metadata. It is common to set up data libraries in SAS metadata but not register tables. In that case DICTIONARY tables are your only option.  

Please bear in mind that defining SAS libraries in metadata is not compulsory and any LIBNAMEs that are created in code will not be stored in metadata.

This post on the same topic may be helpful: https://communities.sas.com/t5/Administration-and-Deployment/Getting-all-libraries-from-meta-data/m-...

[gwootton · Accepted solution]

From your example it looks like you're trying to get a listing of libraries defined in Metadata and their associated datasets and then tying that to the creator, then displaying information about that creator. Is that correct?

Several of these relationships are one-to-many (library to dataset, users to groups, users to emails, users to logins) this might be complicated to produce without identifying which object you want.

In the example below for each table I look up it's associated libref, responsible party and pull that user's display name, and any login associated with DefaultAuth. 

data libtab;
length type id turi luri rpuri curi uiduri duri $ 50 tblname $ 255 libref $ 8 owner dname domname uid $ 255 ;
call missing (of _character_);
keep tblname libref owner uid;
obj="PhysicalTable?@Id contains '.'";
tablecount=metadata_resolve(obj,type,id);
put "NOTE: Found " tablecount "tables.";
if tablecount > 0 then do i = 1 to tablecount;
 rc=metadata_getnobj(obj,i,turi);
 rc=metadata_getattr(turi,"Name",tblname);
 rc=metadata_getnasn(turi,"TablePackage",1,luri);
 rc=metadata_getattr(luri,"Libref",libref);
 rc=metadata_getnasn(turi,"ResponsibleParties",1,rpuri);
 rc=metadata_getnasn(rpuri,"Persons",1,curi);
 rc=metadata_getattr(curi,"Name",owner);
 rc=metadata_getattr(curi,"DisplayName",dname);
 if dname ne "" then owner=dname;
 logincount=metadata_getnasn(curi,"Logins",1,uiduri);
 if logincount > 0 then do j=1 to logincount;
  rc=metadata_getnasn(curi,"Logins",j,uiduri);
  rc=metadata_getnasn(uiduri,"Domain",1,duri);
  rc=metadata_getattr(duri,"Name",domname);
  if domname = "DefaultAuth" then do;
    rc=metadata_getattr(uiduri,"UserID",uid);
  end;
 end;
 output;
 call missing (tblname,libref,owner,uid);
end;
run;

proc print data=libtab; run;

[Kal_ETL]

Hello @gwootton , this is pretty much what I am looking for. It is all the tables and libraries, and the users that have access to those tables and the user that owns that table or library as let's say power user.

This can be a good start since I tried it.

Thank you

[gwootton]

The code provided does not reflect permissions or multiple users. It is only the table, that tables libref, and the user assigned as the responsible party (which could be modified in SMC). If that user has a userID in the defaultauth domain, it also pulls that.