BookmarkSubscribeRSS Feed
Patrick
Opal | Level 21
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#
11 REPLIES 11
data_null__
Jade | Level 19
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.
Patrick
Opal | Level 21
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
data_null__
Jade | Level 19
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.
Patrick
Opal | Level 21
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;
DanielSantos
Barite | Level 11
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
deleted_user
Not applicable
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)
Patrick
Opal | Level 21
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
deleted_user
Not applicable
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.
Cynthia_sas
SAS Super FREQ
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
Patrick
Opal | Level 21
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
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1880 views
  • 0 likes
  • 5 in conversation