BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yashpande
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RahulG
Barite | Level 11

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

8 REPLIES 8
RahulG
Barite | Level 11

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
Obsidian | Level 7
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
Barite | Level 11

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
Obsidian | Level 7
This is perfect. However can you also help me in the report having source columns as well ? doesnt matter size of dataset
RahulG
Barite | Level 11

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
SAS Super FREQ

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
Obsidian | Level 7
Superb. Thanks a ton
lauralawton
SAS Employee

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

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 connect to databases in SAS Viya

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.

Discussion stats
  • 8 replies
  • 5252 views
  • 9 likes
  • 4 in conversation