@arunnz
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;
... View more
