SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Proc Metadata to get Dependent Objects

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Proc Metadata to get Dependent Objects

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.


Accepted Solutions
Solution
‎06-13-2016 08:29 AM
Super Contributor
Posts: 266

Re: Proc Metadata to get Dependent Objects

Posted in reply to yashpande

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.

View solution in original post


All Replies
Super Contributor
Posts: 266

Re: Proc Metadata to get Dependent Objects

Posted in reply to yashpande

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;

Contributor
Posts: 44

Re: Proc Metadata to get Dependent Objects

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
Solution
‎06-13-2016 08:29 AM
Super Contributor
Posts: 266

Re: Proc Metadata to get Dependent Objects

Posted in reply to yashpande

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.

Contributor
Posts: 44

Re: Proc Metadata to get Dependent Objects

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

Re: Proc Metadata to get Dependent Objects

Posted in reply to yashpande

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.

SAS Super FREQ
Posts: 708

Re: Proc Metadata to get Dependent Objects

Posted in reply to yashpande

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

Contributor
Posts: 44

Re: Proc Metadata to get Dependent Objects

Posted in reply to yashpande
Superb. Thanks a ton
Community Manager
Posts: 23

Re: Proc Metadata to get Dependent Objects

Posted in reply to yashpande

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 809 views
  • 6 likes
  • 4 in conversation