DATA Step, Macro, Functions and more

How to read column metadat definitions with SAS program?

Reply
Respected Advisor
Posts: 4,173

How to read column metadat definitions with SAS program?

Hi all

Can someone suggest a way (example code) of how to read metadata for
external files from within a SAS program. What I would need is to pass
the metadata column definitions to a SAS program for further
processing.

What I have:
A bunch of pipe delimeted text files and metadata (external files) for
these text files.

What I would like to achieve:
Create data step views to browse the text files using SAS EG. I'm
thinking of querying the metadata and then based on the metadata
definitions generate the infile statement.

The text files have a header and trailer line and part of the filename
contains the date (changing filenames). That's why I can't just use
the metadata definitions to drill through to the data.
The column definitions (file layouts) are also still subjects to
change (done in metadata).


Thanks in advance
Patrick


P.S: I've posted the same question under
http://groups.google.com/group/comp.soft-sys.sas/browse_thread/thread/ccbffd70ee8366da#
Respected Advisor
Posts: 3,799

Re: How to read column metadat definitions with SAS program?

Post a sample of the meta data files. It is "just" a matter of reading the info from the meta data files to create code, define data sets, and define variables for those data sets, infile/input statement(s) to read data from the data files. It will be easier to show you using a sample of your actual data.
Respected Advisor
Posts: 4,173

Re: How to read column metadat definitions with SAS program?

Posted in reply to data_null__
Hi "data _null_"

To get a piece of SAS code illustrating how to query the Foundation repository and retrieve the column definitions (name, label, length, format, informat) of an ExternalFile (metadata object) is "all" I need. I think I can manage the rest.

What I have is Metaserver, Metaport, User, Passwort and the name of the ExternalFile.

Not sure how I could post sample metadata without the ability to attach a .spk file.

"Scanning" through docs like "Language Interfaces to Metadata" I feel kind of lost and confused. I think I got already some understanding how this "metadata stuff" works but I'm still missing the big picture.

I find in the SAS doc functions like "METADATA_GETPROP" but I still lack the understanding on what object I have to query to get the desired result.

What learning path/reading would you suggest to get up to speed?

Thanks
Patrick P.S: It's SAS 9.2 and External File Metadata is created with SAS DI 4.2


Message was edited by: Patrick
Respected Advisor
Posts: 3,799

Re: How to read column metadat definitions with SAS program?

I'm not going to be any help. I did not understand your question. You did not mention the SAS meta data server bits. I thought you had files with descriptions of data files that you wanted to read and do code gen to create SAS data sets.

I suppose it is "the same thing" but I don't know anything about the SAS meta data server.
Respected Advisor
Posts: 4,173

Re: How to read column metadat definitions with SAS program?

Posted in reply to data_null__
Hi all
I had a steep learning curve today and now just wanted to share this piece of code.
It does query Metadata where the input is the name of an ExternalTable and the output the column definitions for this table; which then could be used to generate an input statement.
Hope this is of some interest.
Patrick


%let metaserver=;
%let metaport=8561;
%let usr=;
%let pw=;

%let ExternalTable_Name='';

options metaserver="&metaserver"
metarepository="Foundation"
metaport=&metaport
metauser="&usr"
metapass="&pw";

data want;
length uri_ExternalTable $256;
length uri_ColumnsAssociation $256;
length uri_OwningFile $256;
length FileName $1000;
length ExternalTable_Name $32;
length SASColumnName $32;
length Desc $256;
length SASColumnLength $5.;
length SASColumnType $1;
length SASFormat $16;
length SASInformat $16;

keep FileName ExternalTable_Name SASColumnName Desc SASColumnLength SASColumnType SASFormat SASInformat ;

call missing(of _all_);

ExternalTable_Name=&ExternalTable_Name;

/* query metadata to retrieve path and name of related physical external file */
rcF=metadata_getnasn(uri_ExternalTable,'OwningFile',n,uri_OwningFile);
rcF=metadata_getattr(uri_OwningFile,"FileName",FileName);


/* query metadata to retrieve URI of external table (called ExternalFile in SAS DI...) */
rc=metadata_getnobj("omsobj:ExternalTable?@Name =&ExternalTable_Name",1,uri_ExternalTable);

/* loop over external table to read metadata of all associated columns */
n=1;
do while(rc>0);
rc=metadata_getnasn(uri_ExternalTable,'Columns',n,uri_ColumnsAssociation);
n+1;
put uri_ColumnsAssociation=;
if rc>0 then
do;
rc1=metadata_getattr(uri_ColumnsAssociation,"SASColumnName",SASColumnName);
rc1=metadata_getattr(uri_ColumnsAssociation,"Desc",Desc);
rc1=metadata_getattr(uri_ColumnsAssociation,"SASColumnLength",SASColumnLength);
rc1=metadata_getattr(uri_ColumnsAssociation,"SASColumnType",SASColumnType);
rc1=metadata_getattr(uri_ColumnsAssociation,"SASFormat",SASFormat);
rc1=metadata_getattr(uri_ColumnsAssociation,"SASInformat",SASInformat);
output;
end;
end;
run;

proc print data=want;
run;
Super Contributor
Posts: 474

Re: How to read column metadat definitions with SAS program?

Nice job.

I kind of prefer to build a convenient XML query and pass it to the METADATA procedure.

Here some doc about it:
http://support.sas.com/documentation/cdl/en/lrmeta/60739/HTML/default/a003177570.htm

Both will do the same.

METADATA procedure is best if you wish to extract a considerable amount of info from the repository(ies), since the answer is returned in a XML file (which can be imported into a SAS dataset through the XML/XMLMAP engine).

Cheers from Portugal.

Daniel Santos @ www.cgd.pt
N/A
Posts: 0

Re: How to read column metadat definitions with SAS program?

Posted in reply to DanielSantos
Might be a bit late, but there's a document I co-wrote for SGF2008 here : http://www2.sas.com/proceedings/forum2008/134-2008.pdf

(Sorry, I keep blowing my own trumpet but we did try to make it a very simple introduction in how-to)
Respected Advisor
Posts: 4,173

Re: How to read column metadat definitions with SAS program?

Posted in reply to deleted_user
Paul

Thanks for that.

I actually found and read your paper already and found it the single most helpful resource so far for someone like me only starting to deal with querying metadata.

The other thing which really helped me to find my way was "metabrowse". I haven't found another source yet which shows me the metadata structure so clear and dynamic.

Right now I've written a (only partial yet) "application" to sync SAS tables from the corresponding metadata table definitions as SMC gives us only the possibility to sync metadata with the underlying SAS tables (but not the other way round).

Developping such a sync program turned out to be quite tricky - especially when it comes to keys and indexes.

I couldn't find a already existing add-on for this syncing. Do you know if one exists for SAS 9.2?

Cheers
Patrick
N/A
Posts: 0

Re: How to read column metadat definitions with SAS program?

Hi Patrick,
Glad to here the paper is 'out there' and providing a useful resource, I took us an age to get to any sort of comfortable stage with the metadata, but we eventually took it to the stage of creating clones of DI processes and even managed to create an impact analysis type process which changed column lengths etc throughout the chain. Stick with it, you'll have hours of fun ;-)

I've not actually worked with 9.2 yet, I left Amadeus just before the paper was presented in Texas and have been working with 9.1.3 since then

What your trying to do sounds great, but we'd struggle to use anything like that here as we use SPD Server clustering which is very temperamental about how the data is created.
SAS Super FREQ
Posts: 8,868

Re: How to read column metadat definitions with SAS program?

Hi:
When you use SAS Data Integration Studio (DI Studio), you are working with either pieces of the SAS Enterprise Intelligence Platform or with the whole Enterprise BI Server configuration. Since this is a whole client-server configuration that represent configuration tiers (application-tier, data-tier, server-tier, web-tier) and that uses the Metadata server for ALL table, user, library, information map, olap cube, etc definitions, how you retrieve Metadata information will be a specialized topic. You are more likely to find folks who are familiar with "metadata" terminology and concepts in the Web Report Studio forum.

As you have discovered, the workings of the Metadata server and whole BI Platform is not as simple a topic as how to use the LIBNAME engine to point to a SAS dataset or how to write a DATA step program to read a flat file. When we teach our "big picture" classes on how to use the Enterprise Intelligence Platform, we talk about the Metadata, but we don't actually discuss the structure or maintenance of the Metadata. Those topics are reserved for the Platform Administration classes, as described here:
http://support.sas.com/training/us/paths/pa.html and
https://support.sas.com/edu/schedules.html?ctry=us&id=330 (the getting started course)
https://support.sas.com/edu/schedules.html?ctry=us&id=329 (the fast track 5-day course)

A discussion of the Metadata and how to safely access the Metadata and the Foundation repository programmatically is a topic that is not easily answered in the space limitations of the Community Forum. You might wish to have this discussion with the people who installed and configured the Enterprise Intelligence Platform at your site. Or, you can also open a track with Tech Support for help with specific Metadata query and maintenance tasks.

cynthia
Respected Advisor
Posts: 4,173

Re: How to read column metadat definitions with SAS program?

Posted in reply to Cynthia_sas
Hi Cynthia

I've done both the Platform Admin Fast Track and the BI Fast Track. None of these 2 trainings cover how to programatically manipulate metadata (I'm happy with queries, I believe to alter metadata programatically is too risky).

I think I understood the content of the 2 courses and put already quite a bit of it into practice. This means for metadata mainly using DIS and SMC or may be one of the pre-defined macros like omabackup.

It would surprise me if the average config and installl person has the skills I'm after. May be there is someone in Tech Support with the knowledge - but I would prefer to gain a better understanding of the metadata model on my own instead of bothering Tech Support with usage questions.

Thanks
Patrick
SAS Super FREQ
Posts: 8,868

Re: How to read column metadat definitions with SAS program?

After the classes, then, it's just a matter of practice --and-- "bonding" with the documentation.

The documentation has changed a bit between 9.1.3 and 9.2, but I think the Open Metadata Architecture Reference and Usage Guide and PROC INFOMAPS and PROC METADATA/METALIB is where you'd go next:
http://support.sas.com/documentation/cdl/en/omaref/59983/PDF/default/omaref.pdf
http://support.sas.com/documentation/cdl/en/engimap/61078/HTML/default/a003052327.htm
http://support.sas.com/documentation/cdl/en/engimap/61078/HTML/default/a003052344.htm
http://support.sas.com/documentation/cdl/en/lrmeta/60739/HTML/default/a003181484.htm
http://support.sas.com/documentation/cdl/en/lrmeta/60739/HTML/default/a003105518.htm

Possibly the Integration Technologies Developer's Guide might have some good architecture background. And the 9.2 Metadata Model documentation has some nice diagrams and explanations:
http://support.sas.com/documentation/cdl/en/lrmeta/60739/HTML/default/a003091804.htm
http://support.sas.com/documentation/cdl/en/lrmeta/60739/HTML/default/a002595408.htm

If you have usage questions about how to query the Metadata it is appropriate to ask Tech Support for help. It will help you and in addition, if Tech Support gets feedback that the documentation needs a different usage case, then they can provide that feedback to the developers and the folks who write the documentation.

cynthia
Ask a Question
Discussion stats
  • 11 replies
  • 443 views
  • 0 likes
  • 5 in conversation