Proc Metadata to get Dependent Objects

[yashpande]

Hi There,

We have got SAS DI Jobs and I am trying to findout all list of SAS Jobs present in repository and its inputs and Outputs tables. 

I have heard a lot about PROC Metadata and have gone through the SAS Docs , however I couldnot find the information on looping through the entire repository.Some direction to the approach would be really appreiated.

[RahulG]

This below code is using data step metadata function.

Note -

nobj = metadata_getnobj("omsobj:Job?@Name contains ''", n, job_uri);

It would run for all jobs in foundation repository. In case you want to test for a particular job then update the statement as shown below

nobj = metadata_getnobj("omsobj:Job?@Name contains ''<Jobname without .sas >", n, job_uri);

**********************************************************
options metaserver="<server>"
metaport=8561
metaprotocol=bridge
metarepository="Foundation"
metauser=<user name>
metapass=<password>;

data Job_input_output;

length job_name job_uri ctrl_order_uri step_uri step_name src_uri src_lib_uri tgt_lib_uri tree_uri jf_uri job_name src_name src_lib_nm tgt_uri tgt_name tgt_lib_nm $256 ;

/* To initialize variables */ nobj = 1; n = 0;

keep job_name step_name src_name src_lib_nm tgt_name tgt_lib_nm lib_uri ;

do while(nobj >= 0 ); /* Loop start- Determine how many jobs are on this repository and identify inputs & outputs. */
n = n+1;

/* To get the number of jobs */
nobj = metadata_getnobj("omsobj:Job?@Name contains ''", n, job_uri);
put "nobj =" nobj "n = " n;
if n = 1 then do;
call symput('nobj', trim(left(nobj))); /* Number of Job objects found. */
if nobj lt 1 then do;
put 'Note: No Job objects found in this repository.';
stop;
end;
end;

/* To get job name */
rc= metadata_getattr(job_uri,'Name',job_name);
rc= metadata_getattr(job_uri,'Name',job_name);
rc_jf = metadata_getnasn(job_uri, 'JFJobs', 1, jf_uri);

/* get the Custom association - control order */
rc = metadata_getnasn(job_uri, 'CustomAssociations', 1, ctrl_order_uri);
/*find out how many steps are associated */
n_steps = metadata_getnasn(ctrl_order_uri, 'AssociatedObjects', 1, step_uri);

do s = 1 to n_steps; /*Loop start - to obtain all transfromation in a job */

/* get details of each step*/
Transformation_step=s;
rc = metadata_getnasn(ctrl_order_uri, 'AssociatedObjects', s, step_uri);
rc = metadata_getattr(step_uri, 'Name', step_name);

/* for each Step, get the Transformations assoc. */
uri = step_uri;
rc = metadata_getnasn(uri, 'Transformations', 1, step_uri);

rc_s = metadata_getnasn(step_uri, 'ClassifierSources', 1, src_uri);
c=0;
do while(c <rc_s); /* Loop start- to obtain source table and its library name */
/* to reset all table name and their uri*/
src_lib_uri=""; src_lib_nm=""; src_uri=""; src_name="";
c=c+1;
rc = metadata_getnasn(step_uri, 'ClassifierSources', c, src_uri);
rc = metadata_getattr(src_uri, 'TableName', src_name);
if lengthn(src_name) =0 then rc = metadata_getattr(src_uri, 'SASTableName', src_name);
if lengthn(src_name) =0 then rc = metadata_getattr(src_uri, 'Name', src_name);
rc = metadata_getnasn(src_uri, 'TablePackage',1, src_lib_uri);
rc = metadata_getattr(src_lib_uri, 'Name',src_lib_nm);
if src_lib_nm ne '' then output;
end; /* Loop end- to obtain source table and its library name */

/* to reset all table name and their uri*/
src_lib_uri=""; src_lib_nm=""; src_uri=""; src_name="";

rc_t = metadata_getnasn(step_uri, 'ClassifierTargets', 1, tgt_uri);
c=0;

do while(c <rc_t); /* Loop start- to obtain target table and its library name */
/* to reset all table name and their uri*/
tgt_lib_uri=""; tgt_lib_nm=""; tgt_uri=""; tgt_name="";
c=c+1;
rc = metadata_getnasn(step_uri, 'ClassifierTargets', c, tgt_uri);
rc = metadata_getattr(tgt_uri, 'TableName', tgt_name);
if lengthn(tgt_name) =0 then rc = metadata_getattr(tgt_uri, 'SASTableName', tgt_name);
if lengthn(tgt_name) =0 then rc = metadata_getattr(tgt_uri, 'Name', tgt_name);
rc = metadata_getnasn(tgt_uri, 'TablePackage',1, tgt_lib_uri);
rc = metadata_getattr(tgt_lib_uri, 'Name',tgt_lib_nm);
if tgt_lib_nm ne '' then output;
end; /* Loop end- to obtain target table and its library name */

/* to reset all table name and their uri*/
tgt_lib_uri=""; tgt_lib_nm=""; tgt_uri=""; tgt_name="";

end; /*Loop end - to obtain all transfromation in a job */

end;/* Loop end- Determine how many jobs are on this repository and identify inputs & outputs. */
run;

[yashpande]

Can we also add the column names in this report for each transformation ? E.g. Extract has 10 columns and also has new derived variable in output and I want to display that details as well. Need some guidance on this

[RahulG · Accepted solution]

I have added a small loop to add TRANSFORMATION target table column name. 

Source table column name not included yet as it will make the output dataset more bulky.

col_cnt=0;
do while(col_cnt <rc_cols);
col_cnt=col_cnt+1;
tgt_col_uri=""; tgt_col_nm="";
rc = metadata_getnasn(tgt_uri, 'Columns',col_cnt, tgt_col_uri);
rc = metadata_getattr(tgt_col_uri, 'Name',tgt_col_nm);
output;
end;
**********************************************************
options metaserver="<server>"
metaport=8561
metaprotocol=bridge
metarepository="Foundation"
metauser=<user name>
metapass=<password>;



data Job_input_output;
length job_name job_uri ctrl_order_uri step_uri step_name src_uri src_lib_uri tgt_lib_uri tree_uri jf_uri job_name src_name src_lib_nm tgt_uri tgt_name
tgt_lib_nm tgt_col_uri tgt_col_nm $256 ;
/* To initialize variables */ nobj = 1; n = 0;
keep job_name step_name src_name src_lib_nm tgt_name tgt_lib_nm lib_uri tgt_col_nm ;

do while(nobj >= 0 ); /* Loop start- Determine how many jobs are on this repository and identify inputs & outputs. */
n = n+1;
/* To get the number of jobs */
nobj = metadata_getnobj("omsobj:Job?@Name contains ''", n, job_uri);
put "nobj =" nobj "n = " n;
if n = 1 then do;
call symput('nobj', trim(left(nobj))); /* Number of Job objects found. */
if nobj lt 1 then do;
put 'Note: No Job objects found in this repository.';
stop;
end;
end;
/* To get job name */
rc= metadata_getattr(job_uri,'Name',job_name);
rc= metadata_getattr(job_uri,'Name',job_name);
rc_jf = metadata_getnasn(job_uri, 'JFJobs', 1, jf_uri);
/* get the Custom association - control order */
rc = metadata_getnasn(job_uri, 'CustomAssociations', 1, ctrl_order_uri);
/*find out how many steps are associated */
n_steps = metadata_getnasn(ctrl_order_uri, 'AssociatedObjects', 1, step_uri);

do s = 1 to n_steps; /*Loop start - to obtain all transfromation in a job */

/* get details of each step*/
Transformation_step=s;
rc = metadata_getnasn(ctrl_order_uri, 'AssociatedObjects', s, step_uri);
rc = metadata_getattr(step_uri, 'Name', step_name);
/* for each Step, get the Transformations assoc. */
uri = step_uri;
rc = metadata_getnasn(uri, 'Transformations', 1, step_uri);
rc_s = metadata_getnasn(step_uri, 'ClassifierSources', 1, src_uri);
c=0;
do while(c <rc_s); /* Loop start- to obtain source table and its library name */
/* to reset all table name and their uri*/
src_lib_uri=""; src_lib_nm=""; src_uri=""; src_name="";
c=c+1;
rc = metadata_getnasn(step_uri, 'ClassifierSources', c, src_uri);
rc = metadata_getattr(src_uri, 'TableName', src_name);
if lengthn(src_name) =0 then rc = metadata_getattr(src_uri, 'SASTableName', src_name);
if lengthn(src_name) =0 then rc = metadata_getattr(src_uri, 'Name', src_name);
rc = metadata_getnasn(src_uri, 'TablePackage',1, src_lib_uri);
rc = metadata_getattr(src_lib_uri, 'Name',src_lib_nm);
/*if src_lib_nm ne '' then*/
output;
end; /* Loop end- to obtain source table and its library name */
/* to reset all table name and their uri*/
src_lib_uri=""; src_lib_nm=""; src_uri=""; src_name="";
rc_t = metadata_getnasn(step_uri, 'ClassifierTargets', 1, tgt_uri);
c=0;
do while(c <rc_t); /* Loop start- to obtain target table and its library name */
/* to reset all table name and their uri*/
tgt_lib_uri=""; tgt_lib_nm=""; tgt_uri=""; tgt_name="";
c=c+1;
rc = metadata_getnasn(step_uri, 'ClassifierTargets', c, tgt_uri);
rc = metadata_getattr(tgt_uri, 'TableName', tgt_name);
if lengthn(tgt_name) =0 then rc = metadata_getattr(tgt_uri, 'SASTableName', tgt_name);
if lengthn(tgt_name) =0 then rc = metadata_getattr(tgt_uri, 'Name', tgt_name);
rc = metadata_getnasn(tgt_uri, 'TablePackage',1, tgt_lib_uri);
rc = metadata_getattr(tgt_lib_uri, 'Name',tgt_lib_nm);
rc_cols = metadata_getnasn(tgt_uri, 'Columns',1, tgt_col_uri);
col_cnt=0;
do while(col_cnt <rc_cols);
col_cnt=col_cnt+1;
tgt_col_uri=""; tgt_col_nm="";
rc = metadata_getnasn(tgt_uri, 'Columns',col_cnt, tgt_col_uri);
rc = metadata_getattr(tgt_col_uri, 'Name',tgt_col_nm);
output;
end;
if rc_cols<=0 then output;

end; /* Loop end- to obtain target table and its library name */

/* to reset all table name and their uri*/
tgt_lib_uri=""; tgt_lib_nm=""; tgt_uri=""; tgt_name="";
end; /*Loop end - to obtain all transfromation in a job */
end;/* Loop end- Determine how many jobs are on this repository and identify inputs & outputs. */
run;

If you feel it helped you then mark it as Solution or give a like.

[yashpande]

This is perfect. However can you also help me in the report having source columns as well ? doesnt matter size of dataset

[RahulG]

Now I have added source column names as well.

options metaserver="<server>"
metaport=8561
metaprotocol=bridge
metarepository="Foundation"
metauser=<user name>
metapass=<password>;

data Job_input_output;
length job_name job_uri ctrl_order_uri step_uri step_name src_uri src_lib_uri tgt_lib_uri tree_uri jf_uri job_name src_name src_lib_nm src_col_uri src_col_nm tgt_uri tgt_name
tgt_lib_nm tgt_col_uri tgt_col_nm $256 ;
/* To initialize variables */ nobj = 1; n = 0;
keep job_name step_name src_name src_lib_nm src_col_nm tgt_name tgt_lib_nm lib_uri tgt_col_nm ;
do while(nobj >= 0 ); /* Loop start- Determine how many jobs are on this repository and identify inputs & outputs. */
n = n+1;
/* To get the number of jobs */
nobj = metadata_getnobj("omsobj:Job?@Name contains ''", n, job_uri);
put "nobj =" nobj "n = " n;
if n = 1 then do;
call symput('nobj', trim(left(nobj))); /* Number of Job objects found. */
if nobj lt 1 then do;
put 'Note: No Job objects found in this repository.';
stop;
end;
end;
/* To get job name */
rc= metadata_getattr(job_uri,'Name',job_name);
rc= metadata_getattr(job_uri,'Name',job_name);
rc_jf = metadata_getnasn(job_uri, 'JFJobs', 1, jf_uri);
/* get the Custom association - control order */
rc = metadata_getnasn(job_uri, 'CustomAssociations', 1, ctrl_order_uri);
/*find out how many steps are associated */
n_steps = metadata_getnasn(ctrl_order_uri, 'AssociatedObjects', 1, step_uri);
do s = 1 to n_steps; /*Loop start - to obtain all transfromation in a job */
/* get details of each step*/
Transformation_step=s;
rc = metadata_getnasn(ctrl_order_uri, 'AssociatedObjects', s, step_uri);
rc = metadata_getattr(step_uri, 'Name', step_name);
/* for each Step, get the Transformations assoc. */
uri = step_uri;
rc = metadata_getnasn(uri, 'Transformations', 1, step_uri);
rc_s = metadata_getnasn(step_uri, 'ClassifierSources', 1, src_uri);
c=0;
do while(c <rc_s); /* Loop start- to obtain source table and its library name */
/* to reset all table name and their uri*/
src_lib_uri=""; src_lib_nm=""; src_uri=""; src_name="";
c=c+1;
rc = metadata_getnasn(step_uri, 'ClassifierSources', c, src_uri);
rc = metadata_getattr(src_uri, 'TableName', src_name);
if lengthn(src_name) =0 then rc = metadata_getattr(src_uri, 'SASTableName', src_name);
if lengthn(src_name) =0 then rc = metadata_getattr(src_uri, 'Name', src_name);
rc = metadata_getnasn(src_uri, 'TablePackage',1, src_lib_uri);
rc = metadata_getattr(src_lib_uri, 'Name',src_lib_nm);
rc_src_cols = metadata_getnasn(src_uri, 'Columns',1, src_col_uri);
src_col_cnt=0;
do while(src_col_cnt <rc_src_cols);
src_col_cnt=src_col_cnt+1;
src_col_uri=""; src_col_nm="";
rc = metadata_getnasn(src_uri, 'Columns',src_col_cnt, src_col_uri);
rc = metadata_getattr(src_col_uri, 'Name',src_col_nm);
output;
end;
if rc_src_cols<=0 then output;
src_col_uri=""; src_col_nm="";
end; /* Loop end- to obtain source table and its library name */
/* to reset all table name and their uri*/
src_lib_uri=""; src_lib_nm=""; src_uri=""; src_name="";
rc_t = metadata_getnasn(step_uri, 'ClassifierTargets', 1, tgt_uri);
c=0;
do while(c <rc_t); /* Loop start- to obtain target table and its library name */
/* to reset all table name and their uri*/
tgt_lib_uri=""; tgt_lib_nm=""; tgt_uri=""; tgt_name="";
c=c+1;
rc = metadata_getnasn(step_uri, 'ClassifierTargets', c, tgt_uri);
rc = metadata_getattr(tgt_uri, 'TableName', tgt_name);
if lengthn(tgt_name) =0 then rc = metadata_getattr(tgt_uri, 'SASTableName', tgt_name);
if lengthn(tgt_name) =0 then rc = metadata_getattr(tgt_uri, 'Name', tgt_name);
rc = metadata_getnasn(tgt_uri, 'TablePackage',1, tgt_lib_uri);
rc = metadata_getattr(tgt_lib_uri, 'Name',tgt_lib_nm);
rc_cols = metadata_getnasn(tgt_uri, 'Columns',1, tgt_col_uri);
col_cnt=0;
do while(col_cnt <rc_cols);
col_cnt=col_cnt+1;
tgt_col_uri=""; tgt_col_nm="";
rc = metadata_getnasn(tgt_uri, 'Columns',col_cnt, tgt_col_uri);
rc = metadata_getattr(tgt_col_uri, 'Name',tgt_col_nm);
output;
end;
if rc_cols<=0 then output;
tgt_col_uri=""; tgt_col_nm="";
end; /* Loop end- to obtain target table and its library name */
/* to reset all table name and their uri*/
tgt_lib_uri=""; tgt_lib_nm=""; tgt_uri=""; tgt_name="";
end; /*Loop end - to obtain all transfromation in a job */
end;/* Loop end- Determine how many jobs are on this repository and identify inputs & outputs. */
run;

do not forget to like the reply.

[BrunoMueller]

Hi

How about using the sas-relationship-reporter. Have a look at this blog entry http://blogs.sas.com/content/sgf/2015/03/04/relationships-are-easy-for-sas-objects/

More details on the relationship reporting tool can be found here http://support.sas.com/documentation/cdl/en/bisag/68240/HTML/default/viewer.htm#p1dcleibglhr6wn19yzv...

Bruno

[yashpande]

Superb. Thanks a ton

[lauralawton]

Hello yashpande,

If one of the replies was the exact solution to your problem, can you please "Accept it as a solution"? Or if one was particularly helpful, feel free to "Like" it. This will help other community members who may run into the same issue know what worked.

Regards,

Laura