List of DI Studio job Output Tables

[L0007]

Hi ,

I have SAS jobs in SAS EG.

 For each job, I would like to have related output tables (after Table Loader Step).

E.g. I have a JOB_A with two output (TABLE_A & TABLE_B)

I would like to have something like:

JOB_A | TABLE_A

JOB_A | TABLE_B

I don't know how to perform this with SAS code?

Can you help with this?

Thanks
 

[ballardw]

---

@L0007 wrote:

Hi ,

I have SAS jobs in SAS EG.

 For each job, I would like to have related output tables (after Table Loader Step).

E.g. I have a JOB_A with two output (TABLE_A & TABLE_B)

I would like to have something like:

JOB_A | TABLE_A

JOB_A | TABLE_B

I don't know how to perform this with SAS code?

Can you help with this?

Thanks
 

---

Where do you expect this to appear? In the name of a data set? A label for a data set? Text in a report title?Footnote? Somewhere else in a report? A label in the flow?

[L0007]

Hi @ballardw

I would like to have information in a summary table.

This summary table should have 2 columns :

• jobname
• outputname

Just to be more precise:

I would like to build a program that will look a list of jobs (by jobname?) and return the summary table.

If 4 jobs exist, I would like a summary table like this

JOBNAME | OUTPUTNAME

------------------------

JobA.sas | table A

JobA.sas | table B

JobC.sas | table D

JobF.sas | (missing)

JobE.sas | TableE 

[ballardw]

Since my previous post was only questions it is not an actual solution and you should remove the solution marker.

[Patrick]

I would understand the term "Table Loader Step" in the context of SAS DIS or Viya Studio Flow - but what is the meaning of it in the context of SAS EG?

For what you want: I believe you would need to write your own macro to collect such information. Potentially using Proc Scaproc in such a macro would give you what you want - but not sure.

Suggest you show us in more details what you've got (like a picture of a relevant EG flow) and the code for your "table loader" for us to understand better what could be suitable.

For the job name: Would this be the EG flow name or the EG node name ...or something else? And would it only need to work for programs run via EG?

Here some sample code how such log collection using Proc Scaproc could look like. Would of course need "generalization" via macro code for repeated use.

data transactions;
  set sashelp.class;
run;

/* collect log info */
filename collect temp;
proc scaproc; 
   record collect; 
run;

proc append data=transactions base=master;
run;

data out1 out2;
  set transactions;
run;

/* write log info to external file */
proc scaproc; 
   write; 
run;

/* parse external file to extract desired info */
data scratch(keep=clienttasklabel taskstarttime procname io processing ds3 _step) 
     proc(keep=_step procname rename=(procname=_prn));
  attrib
    clienttasklabel length=$32  
    taskstarttime   length=8    informat=datetime. format=datetime22.2 
    procname        length=$40. informat=$40.
    io              length=$8   informat=$8.
    processing      length=$8   informat=$8.
    ds3             length=$45  informat=$45.
    ;
  retain procname taskstarttime io processing ds3;
  retain clienttasklabel "&_clienttasklabel" _step 1;

  infile collect truncover dlm=' ';
  input @3 _jobsplit:$8. _type:$20. @;
  if _jobsplit='JOBSPLIT';

  if _type='STEP' then 
    do;
      call missing(taskstarttime, io, processing, ds3);
      output proc;
      _step+1;
    end;
  else if _type='TASKSTARTTIME' then 
    do;
      input taskstarttime;
    end;
  else if _type='PROCNAME' then 
    do;
      input procname;
    end;
  else if _type='DATASET' then 
    do;
      input io processing ds3;
      output scratch;
    end;
/*  else input (_word1-_word4) (:$45.);*/
run;
filename collect;

data scratch;
  merge scratch proc;
  by _step;
  procname=_prn;
  drop _step _prn;
run;

/* print collected info */
proc print data=scratch;
run;

[L0007]

Hi @Patrick ,

You'right.  My apologies.

My jobs are in SAS DI (not SAS EG....  🤕)

I've build a concrete example in DI.

I would like to build a result table like this.

JobName	Output
JobA	OUT_A
JobA	OUT_B

In DI, I can see needed  information in two places:

Job Properties:

Name attribute  & Tables Loaded attrbute.

or in the Header section of the SAS automated code generated in DI for the job.

Job attribute & Target Tables attribute

But I don't know how to do that. 😕

Thanks for your help

[Patrick]

@L0007 

I guess it depends a bit what you want to report on: A) Input/Output tables used per code execution or B) Input/Output tables as defined in DIS as metadata in the job.

I've been a regular DIS user but haven't done anything with it since years now and I currently don't have access to an environment with DIS. Therefore the following from memory.

For B:

There are some built-in metadata reports which might give you the information you need. If not sufficient then you would need to query SAS metadata and build the reports yourself. This can be done but querying SAS metadata gets quickly rather involved. Given your requirement sounds not that exotic it might be worth to search the Internet a bit for some sample code that's already close to what you need. A very quick search got me https://support.sas.com/resources/papers/proceedings12/120-2012.pdf 

For A:

DIS generates macro variables like &input_1, &input_2, &output_1, ....Inspect the generated code to understand what's really available.

If I remember right then these macro variables will have global scope and DIS doesn't generate code to remove them after the step. What this leads to is that an earlier step might have created and populated macro variable &input_3 but your current step has only two inputs (and though only populates &input_1 and &input_2). 

If I remember right then there is some "&input_cnt" variable which you would need to use as well in order to determine how many macro variables to use for populating your log table. 

In my thinking you would need to implement some autocall macro for using these macro variables and add rows to some permanent logging table. You then would call this macro variable as post code in the transformation.

Just search through the DIS generated code to figure out for what information is available in macro variables that would be useful to you. 

...and then there is of course still the option to use Proc Scaproc also implemented as an autocall macro that you call as pre-code to turn Proc Scaproc on and as post-code to parse the generated file and add the information to a permanent logging table.

...If option B) is what you need and you want us to propose some actual code then please attach a .sas file with all the DIS generated code for a whole job (you could mock-up a job with only a table loader in it and share the generated code for this job. Please also add some transformation level dummy pre- and post-code so we can understand exactly where such code gets generated).

Also to consider:

DIS got discontinued under Viya. DIS jobs will need migration to SAS Studio Flow. Also the SAS Metadata Server doesn't exist anymore under Viya. 

Any job querying SAS metadata will likely not migrate and require a full redo under Viya to collect and report on similar information.

I'm also not sure if migrated DIS jobs will still generate these &input_... and &output_... macro variables and though any custom code using these macro variables will potentially need a full revamp.

What should still work without requiring change would be an approach using Proc Scaproc.