Hi Arun
I put together a piece of code that extracts and parse (most) options set in transformation instances. The key in output data is TransformationStepID. It is very difficult to write an "all-in-one" XML extract, so it is recommendable to make many simple extracts and join data later in SAS. So you need a similar extract to return TransformationStepID as a drill-down from jobs in order to get that information to join with. I will se if I can find time to supply the XML's for that.
You might also be interested in my answer to Speedy and the discussion in this link:
https://communities.sas.com/t5/SAS-Data-Management/DI-Studio-mappings/m-p/552684#M16945
The SAS metadata browser is a GREAT help, I could'nt do things like this without it, and it would be a very nice to have as a tool in DI Studio os as a stand-alone solution like SAS XML Mapper, but so far it is integrated in SAS Display Manager and not available as a stand-alone solution. There is a free metadata browser available from Metacoda known for their excellent SAS extensions, but I don't know if it is a full metadata browser or limited to metadata objects relevant for autorisation/security control, which is their speciality.
Here is the code. Change XXX'es to your metadata credentials.
/*****************************************************************************/
/* TransStepOptions */
/* */
/* Extract Options set in transformation instances. */
/* Key in output is TransformationStepID. */
******************************************************************************/
/*------------------------------------------------------------------------------
Build XML extract specification - stored in temporary filename xmlspec.
When it works, put it in a permanent file to use in a production setup.
The XML search argument is ignored with Flags option 260. It is included for
code testing. Change to Flags 388 to use the search argument.
------------------------------------------------------------------------------*/
filename xmlspec temp;
data _null_; file xmlspec;
input;
put _infile_;
cards4;
<GetMetadataObjects>
<Reposid>$METAREPOSITORY</Reposid>
<Type>TransformationStep</Type>
<Objects/>
<Ns>SAS</Ns>
<Flags>260</Flags>
<Options>
<Templates>
<Template TemplateName="TransformationStep">
<TransformationStep ID="" MetadataCreated="" MetadataUpdated="" Name="" TransformRole="">
<PropertySets search="PropertySet[@Name='MACROOPTIONS']">
<PropertySet TemplateName="T1"/>
</PropertySets>
</TransformationStep>
</Template>
<Template TemplateName="T1">
<PropertySet Name="">
<Properties>
<Property/>
</Properties>
</PropertySet>
<Property Id="" Name="" PropertyName="" DefaultValue=""/>
</Template>
</Templates>
<XMLSelect search="TransformationStep[@Id='A5GCJP2N.BR001FC4'"/>
</Options>
</GetMetadataObjects>
;;;;
run;
/*----------------------------------------------------------------------------
Extract metadata as XML file - stored in temporary filename xmlout.
While developing it is useful to extract to a physical file that can be
viewed in a browser, as a help to write the XML map.
----------------------------------------------------------------------------*/
filename xmlout temp;
options metaserver="XXX"
metaport=8561
metaprotocol="BRIDGE"
metauser="XXX"
metapass="XXX"
metarepository="Foundation";
proc metadata out=xmlout header=full in=xmlspec;
run;
quit;
/*----------------------------------------------------------------------------
Build XML map - stored in temporary filename xmlmap.
When it works, put it in a permanent file to use in a production setup.
----------------------------------------------------------------------------*/
filename xmlmap temp;
data _null_;
file xmlmap;
input;
put _infile_;
cards4;
<?xml version="1.0" encoding="UTF-8"?>
<SXLEMAP name="TransformationOptions" version="2.1">
<NAMESPACES count="0"/>
<TABLE name="TransformationOptions">
<TABLE-PATH syntax="XPath">/GetMetadataObjects/Objects/TransformationStep/PropertySets/PropertySet/Properties/Property</TABLE-PATH>
<COLUMN name="TransformationStepID" retain="YES">
<PATH syntax="XPath">/GetMetadataObjects/Objects/TransformationStep/@Id</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>17</LENGTH>
</COLUMN>
<COLUMN name="OptionID">
<PATH syntax="XPath">/GetMetadataObjects/Objects/TransformationStep/PropertySets/PropertySet/Properties/Property/@Id</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>17</LENGTH>
</COLUMN>
<COLUMN name="OptionName">
<PATH syntax="XPath">/GetMetadataObjects/Objects/TransformationStep/PropertySets/PropertySet/Properties/Property/@PropertyName</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>60</LENGTH>
</COLUMN>
<COLUMN name="OptionPrompt">
<PATH syntax="XPath">/GetMetadataObjects/Objects/TransformationStep/PropertySets/PropertySet/Properties/Property/@Name</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>60</LENGTH>
</COLUMN>
<COLUMN name="OptionValue">
<PATH syntax="XPath">/GetMetadataObjects/Objects/TransformationStep/PropertySets/PropertySet/Properties/Property/@DefaultValue</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>32767</LENGTH>
</COLUMN>
</TABLE>
</SXLEMAP>
;;;;
run;
/*----------------------------------------------------------------------------
1. Assign XML libname from temporary files xmlout and xmlmap.
2. Copy tables from XML library to SAS work library.
All table(s) defined in <table> tag(s) in the XML map are copied
----------------------------------------------------------------------------*/
libname xmlout xmlv2 xmlmap=xmlmap access=READONLY;
libname metadata "&datafolder";
proc copy in=xmlout out=work;
run;
/*----------------------------------------------------------------------------
Clear all XML filenames/libnames
----------------------------------------------------------------------------*/
filename xmlspec clear;
filename xmlmap clear;
filename xmlout clear;
libname xmlout clear;
/*----------------------------------------------------------------------------
Parse OptionValue string and extract individual options.
Input table name and column names as in <table> and zcolumn> tag(s) in XML map.
NOTE: Some types are omitted in this example because of parsing difficulties.
----------------------------------------------------------------------------*/
data work.Transstepoptions_unpacked;
set work.Transformationoptions (where=(
OptionName ne 'ADMIN.UPDATEMETADATA'
and index(OptionValue,'definitionType="DataSourceColumnDefinition"') = 0
and OptionValue ne ''
));
drop OptionValue valfound wstr valslut;
length ExtractValue $512 wstr $32767;
wstr = tranwrd(htmldecode(OptionValue),'09'x,' ');
OptNr = 0;
if OptionValue =: '<?xml' then do;
do until(valfound = 0);
valfound = index(wstr,'value="');
if valfound > 0 then do;
wstr = substr(wstr,valfound + 7);
valslut = index(wstr,'">');
if valslut > 1 then do;
ExtractValue = substr(wstr,1,valslut-1);
OptNr = OptNr + 1;
output;
wstr = substr(wstr,valslut+2);
end;
end;
end;
if OptNr = 0 then do until(valfound = 0);
valfound = index(wstr,'fileLocation="');
if valfound > 0 then do;
wstr = substr(wstr,valfound + 14);
valslut = index(wstr,'"');
if valslut > 1 then do;
ExtractValue = substr(wstr,1,valslut-1);
OptNr = OptNr + 1;
output;
wstr = substr(wstr,valslut+2);
end;
end;
end;
if OptNr = 0 then do until(valfound = 0);
valfound = index(wstr,'dataSourceLocation="');
if valfound > 0 then do;
wstr = substr(wstr,valfound + 20);
valslut = index(wstr,'"');
if valslut > 1 then do;
ExtractValue = substr(wstr,1,valslut-1);
OptNr = OptNr + 1;
output;
wstr = substr(wstr,valslut+2);
end;
end;
end;
end;
else do;
OptNr = 1;
ExtractValue = OptionValue;
output;
end;
run;
I don't have DIS at my site, so i can't post details, but: Have you heard of "metabrowse"? This is a utility integrated into SAS display manager and allows browsing the metadata, anything you see there can be extracted using metadata_get* functions.
Hi @arunnz
The full detail level is stored in metadata. If you open an existing job in DI Studio, everything down to the exact position of objects on the canvas is retrieved from metadata, including what you see in the options tab. So what you want is possible, but complicated.
It is rather tricky to retrieve information about user prompts in custom transformations. There are two problems:
Here is an example of a custom transformation and it's use in a job (actually the transformation we use to extract metadata):
Prompts defined in the custom transformation when it was created:
Prompts seen in the transformation instance. Note that the value marked with red is set in the actual instance, while the values marked with green are defaults from the custom transformation left unchanged in this instance:
Here is what is stored in metadata for prompts in the custom transformation:
<PromptGroup promptId="PromptGroup_1469303013736_890681" version="1.0"><DefinitionsAndSubgroups><TextDefinition minValueCount="1" name="Udtraeknavn" promptId="PromptDef_1469302071306_243889"><Label><Text xml:lang="da-DK">Navn på udtraek</Text></Label><Description><Text xml:lang="da-DK">Navn, der bruges til at vælge den relevante XML-specifikationsfil og XML-maptil til dette udtræk</Text></Description></TextDefinition><TextDefinition allowUserValues="false" maxValueCount="3" minValueCount="1" name="Serverliste" promptId="PromptDef_1469302071307_966088"><Label><Text xml:lang="da-DK">Metadata servere</Text></Label><Description><Text xml:lang="da-DK">De metadataservere, der skal udtrækkes data fra</Text></Description><DefaultValue><Collection><Values><String value="sasmetaprod"></String><String value="sasmetaudvk"></String><String value="sasmetatest"></String></Values></Collection></DefaultValue><ValueProvider><StaticValueProvider dataType="StringType"><Values><String index="0" value="sasmetaprod"></String><String index="1" value="sasmetaudvk"></String><String index="2" value="sasmetatest"></String></Values><Labels><Language xml:lang="da-DK"><LabelSet><String index="0" value="sasmetaprod"></String><String index="1" value="sasmetaudvk"></String><String index="2" value="sasmetatest"></String></LabelSet></Language></Labels></StaticValueProvider></ValueProvider></TextDefinition><TextDefinition name="XMLsti" promptId="PromptDef_1469302173518_642292"><Label><Text xml:lang="da-DK">Sti til XML hovedfolder</Text></Label><Description><Text xml:lang="da-DK">Sti til den overfolder, der rummer de 3 krævede foldere XMLspec, XMLmap og XMLresult</Text></Description><DefaultValue><String value="/sasdata/%sysget(SAS_ENVIRONMENT)/data/1_grunddata/sas_metadata/xml/"></String></DefaultValue></TextDefinition><TextDefinition hidden="true" name="ADMIN.DONTDELETEOUTPUTS" promptId="PromptDef_1551777880181_22911"></TextDefinition></DefinitionsAndSubgroups><Label><Text xml:lang="da-DK">Generelt</Text></Label><Description><Text xml:lang="da-DK">Topgruppe for prompter</Text></Description></PromptGroup>
And this is stored in metadata for the actual instance - only the value marked with red above:
<?xml version="1.0"?><PromptDefinitionReference definitionType="TextDefinition" name="Udtraeknavn" promptId="PromptDef_1469302071306_243889" selectionType="Single" version="1.0"><Value><String value="Folder"></String></Value></PromptDefinitionReference>
Our result - This is (part of) the data for an instance of the example custom transformation in a given job:
Do you have a Base SAS installation? - I ask because the structure in metadata is very complicated and the documentation is not easily accessible, so the metadata browser (found under Solutions in Display Manager) is the only way to get a clear picture of the object-association chains in metadata, and the route down to transformation prompts is pretty long.
We have a big daily batch setup where we extract alle relevant information including lineage, dependencies, ownership, physical file- and table names etc. from about 8.700 jobs with 36.600 transformations and 13.800 permanent tables in 3 different metadata servers. We have dropped SAS Data Step Functions and use Metadata Procedures with XML extract specifications and maps, because it takes less than a minute to extract everything, while it took hours with data step functions.
It is not that easy to filter out stand-alone code for you from our big setup, but I will see if I can find time in the following days to come up with something as a start to help you to extract relevant data from custom transformations and unpack the information. Please let me know if you are still interested after this.....
Thank You @ErikLund_Jensen ,
Sorry couldn't respond any sooner to your response.
It's good to hear that everything we plot in DIS can be captured. Unfortunately I don't have Base SAS at my site, I haven't dwell much into XML parsing yet. but keen to learn how this can be implemented, if time permits would you be able to share a code snippet that does the extraction and string parsing. I know its too much to ask but I really stuck with metadata data step function
Also it will be great if you can point me to any doc that's available re extracting prompt values and out DIS job attributes ?
Once again really appreciate the awesome support.
Kind Regards
Arun
Hi Arun
I put together a piece of code that extracts and parse (most) options set in transformation instances. The key in output data is TransformationStepID. It is very difficult to write an "all-in-one" XML extract, so it is recommendable to make many simple extracts and join data later in SAS. So you need a similar extract to return TransformationStepID as a drill-down from jobs in order to get that information to join with. I will se if I can find time to supply the XML's for that.
You might also be interested in my answer to Speedy and the discussion in this link:
https://communities.sas.com/t5/SAS-Data-Management/DI-Studio-mappings/m-p/552684#M16945
The SAS metadata browser is a GREAT help, I could'nt do things like this without it, and it would be a very nice to have as a tool in DI Studio os as a stand-alone solution like SAS XML Mapper, but so far it is integrated in SAS Display Manager and not available as a stand-alone solution. There is a free metadata browser available from Metacoda known for their excellent SAS extensions, but I don't know if it is a full metadata browser or limited to metadata objects relevant for autorisation/security control, which is their speciality.
Here is the code. Change XXX'es to your metadata credentials.
/*****************************************************************************/
/* TransStepOptions */
/* */
/* Extract Options set in transformation instances. */
/* Key in output is TransformationStepID. */
******************************************************************************/
/*------------------------------------------------------------------------------
Build XML extract specification - stored in temporary filename xmlspec.
When it works, put it in a permanent file to use in a production setup.
The XML search argument is ignored with Flags option 260. It is included for
code testing. Change to Flags 388 to use the search argument.
------------------------------------------------------------------------------*/
filename xmlspec temp;
data _null_; file xmlspec;
input;
put _infile_;
cards4;
<GetMetadataObjects>
<Reposid>$METAREPOSITORY</Reposid>
<Type>TransformationStep</Type>
<Objects/>
<Ns>SAS</Ns>
<Flags>260</Flags>
<Options>
<Templates>
<Template TemplateName="TransformationStep">
<TransformationStep ID="" MetadataCreated="" MetadataUpdated="" Name="" TransformRole="">
<PropertySets search="PropertySet[@Name='MACROOPTIONS']">
<PropertySet TemplateName="T1"/>
</PropertySets>
</TransformationStep>
</Template>
<Template TemplateName="T1">
<PropertySet Name="">
<Properties>
<Property/>
</Properties>
</PropertySet>
<Property Id="" Name="" PropertyName="" DefaultValue=""/>
</Template>
</Templates>
<XMLSelect search="TransformationStep[@Id='A5GCJP2N.BR001FC4'"/>
</Options>
</GetMetadataObjects>
;;;;
run;
/*----------------------------------------------------------------------------
Extract metadata as XML file - stored in temporary filename xmlout.
While developing it is useful to extract to a physical file that can be
viewed in a browser, as a help to write the XML map.
----------------------------------------------------------------------------*/
filename xmlout temp;
options metaserver="XXX"
metaport=8561
metaprotocol="BRIDGE"
metauser="XXX"
metapass="XXX"
metarepository="Foundation";
proc metadata out=xmlout header=full in=xmlspec;
run;
quit;
/*----------------------------------------------------------------------------
Build XML map - stored in temporary filename xmlmap.
When it works, put it in a permanent file to use in a production setup.
----------------------------------------------------------------------------*/
filename xmlmap temp;
data _null_;
file xmlmap;
input;
put _infile_;
cards4;
<?xml version="1.0" encoding="UTF-8"?>
<SXLEMAP name="TransformationOptions" version="2.1">
<NAMESPACES count="0"/>
<TABLE name="TransformationOptions">
<TABLE-PATH syntax="XPath">/GetMetadataObjects/Objects/TransformationStep/PropertySets/PropertySet/Properties/Property</TABLE-PATH>
<COLUMN name="TransformationStepID" retain="YES">
<PATH syntax="XPath">/GetMetadataObjects/Objects/TransformationStep/@Id</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>17</LENGTH>
</COLUMN>
<COLUMN name="OptionID">
<PATH syntax="XPath">/GetMetadataObjects/Objects/TransformationStep/PropertySets/PropertySet/Properties/Property/@Id</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>17</LENGTH>
</COLUMN>
<COLUMN name="OptionName">
<PATH syntax="XPath">/GetMetadataObjects/Objects/TransformationStep/PropertySets/PropertySet/Properties/Property/@PropertyName</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>60</LENGTH>
</COLUMN>
<COLUMN name="OptionPrompt">
<PATH syntax="XPath">/GetMetadataObjects/Objects/TransformationStep/PropertySets/PropertySet/Properties/Property/@Name</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>60</LENGTH>
</COLUMN>
<COLUMN name="OptionValue">
<PATH syntax="XPath">/GetMetadataObjects/Objects/TransformationStep/PropertySets/PropertySet/Properties/Property/@DefaultValue</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>32767</LENGTH>
</COLUMN>
</TABLE>
</SXLEMAP>
;;;;
run;
/*----------------------------------------------------------------------------
1. Assign XML libname from temporary files xmlout and xmlmap.
2. Copy tables from XML library to SAS work library.
All table(s) defined in <table> tag(s) in the XML map are copied
----------------------------------------------------------------------------*/
libname xmlout xmlv2 xmlmap=xmlmap access=READONLY;
libname metadata "&datafolder";
proc copy in=xmlout out=work;
run;
/*----------------------------------------------------------------------------
Clear all XML filenames/libnames
----------------------------------------------------------------------------*/
filename xmlspec clear;
filename xmlmap clear;
filename xmlout clear;
libname xmlout clear;
/*----------------------------------------------------------------------------
Parse OptionValue string and extract individual options.
Input table name and column names as in <table> and zcolumn> tag(s) in XML map.
NOTE: Some types are omitted in this example because of parsing difficulties.
----------------------------------------------------------------------------*/
data work.Transstepoptions_unpacked;
set work.Transformationoptions (where=(
OptionName ne 'ADMIN.UPDATEMETADATA'
and index(OptionValue,'definitionType="DataSourceColumnDefinition"') = 0
and OptionValue ne ''
));
drop OptionValue valfound wstr valslut;
length ExtractValue $512 wstr $32767;
wstr = tranwrd(htmldecode(OptionValue),'09'x,' ');
OptNr = 0;
if OptionValue =: '<?xml' then do;
do until(valfound = 0);
valfound = index(wstr,'value="');
if valfound > 0 then do;
wstr = substr(wstr,valfound + 7);
valslut = index(wstr,'">');
if valslut > 1 then do;
ExtractValue = substr(wstr,1,valslut-1);
OptNr = OptNr + 1;
output;
wstr = substr(wstr,valslut+2);
end;
end;
end;
if OptNr = 0 then do until(valfound = 0);
valfound = index(wstr,'fileLocation="');
if valfound > 0 then do;
wstr = substr(wstr,valfound + 14);
valslut = index(wstr,'"');
if valslut > 1 then do;
ExtractValue = substr(wstr,1,valslut-1);
OptNr = OptNr + 1;
output;
wstr = substr(wstr,valslut+2);
end;
end;
end;
if OptNr = 0 then do until(valfound = 0);
valfound = index(wstr,'dataSourceLocation="');
if valfound > 0 then do;
wstr = substr(wstr,valfound + 20);
valslut = index(wstr,'"');
if valslut > 1 then do;
ExtractValue = substr(wstr,1,valslut-1);
OptNr = OptNr + 1;
output;
wstr = substr(wstr,valslut+2);
end;
end;
end;
end;
else do;
OptNr = 1;
ExtractValue = OptionValue;
output;
end;
run;
Thanks again for sharing this code and also for the reference to very insightful thread.
Its a shame I can't access the Display Manager tool to meta browse to see what's available. Also I'm quite new to xml maps so will have to do some research.
I will start trying with the code snippet you have shared and see how it goes, will definitely post the update in this thread. But would like to say I was skeptical when I post this query in the forum, but the support received is so helpful. Thank You again ErickLund
Regards
Arun
Thanks @ErikLund_Jensen for mentioning the Metacoda Metadata Explorer in your post.
Hi @arunnz,
The Metacoda Metadata Explorer is a free tool that is available as a plug-in to the SAS Management Console to search across all SAS metadata (as well as security related metadata). It is primarily search driven and you can browse too. Check out the technical blog post with screenshots and sample queries that you can do using the tool at https://platformadmin.com/blogs/paul/2012/08/metacoda-metadata-explorer-plug-in/
If you would like to use the free Metacoda utilities, please complete the form at https://www.metacoda.com/en/evaluation/ and write a note that you want to use only the free tools. If you want to try out the other Metacoda commercial plug-ins we can provide you a 30 day free evaluation license.
Kind Regards,
Michelle
Hi @arunnz
I am glad to help, and I hope the code works for you. I found this very useful documentation of the different metadata types:
http://support.sas.com/documentation/cdl/en/omamodref/67417/HTML/default/viewer.htm#primarytype.htm
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.