BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Nivas88
Calcite | Level 5

Hi All,

 

We have around 500 schedule jobs in our SMC. I''m trying to list all the flow name and list in a excel sheet. I dont want to copy paste each  names. is there any sas command to extract all the schdeule flow names from metadata and I will do a copy paste into excel one time. Please let me know

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
gwootton
SAS Super FREQ

This program may be helpful:

https://github.com/sascommunities/technical-support-code/blob/main/usage/administration/sas-metadata...

--
Greg Wootton | Principal Systems Technical Support Engineer

View solution in original post

3 REPLIES 3
anuraagjv
SAS Employee

If you run the following code in Enterprise Guide, and then export the output to Excel, you should get quite a good, usable report. Alternatively, you can also run it from command line SAS (sas -nodms)

 

options linesize=150;

ods graphics off;

ods html close;

ods listing;

 

%macro flowdef;

  %if &sysscp = WIN %then

    %let JSHOME = %SYSGET(JS_HOME);

  %else

    %do;

      data _null_;

         infile "ps -ef | grep ""/jfd""" pipe;

         input;

         if index(_infile_,"grep /jfd") then

            delete;

         length JS_PATH $ 200 JSHOME $ 200;

         slash=index(_infile_,"/");

         if slash then

           do;

             JS_PATH = substr(_infile_,slash);

             jsplen  = length(JS_PATH);

             JSHOME  = reverse(substr(JS_PATH,1,jsplen-3));

             do until(compress(scan(JSHOME,1,"/"),"0123456789.")="");

                x=index(substr(JSHOME,2),"/")+2;

                JSHOME=substr(JSHOME,x);

         end;

             x=index(substr(JSHOME,2),"/")+2;

             JSHOME=substr(JSHOME,x);

             JSHOME=reverse(trim(JSHOME));

             call symput("JSHOME",JSHOME);

           end;

      run;

    %end;

 

    %PUT JS_HOME = "%trim(&JSHOME)";

 

  %if %length(%trim(&JSHOME)) = 0 %then

    %do;

      %put %str();

      %put ERROR: JS_HOME environment variable not available, exiting;

      data _null_; abort return 16; run;

    %end;

  %if &sysscp = WIN %then

    filename flowdefs "%trim(&JSHOME)\work\storage\flow_storage\*.dat";

  %else

    filename flowdefs "%trim(&JSHOME)/work/storage/flow_storage/*.dat";

%mend;

 

%flowdef;

 

data scheduled adhoc;

   length owner $ 32 flowname jobname $ 96 runas $ 32 instance $ 8 removed $ 1 TZ $ 9 calendar calowner $ 32 timepart $ 65 nextrun $ 17;

   retain owner      flowname jobname      runas      instance     removed     TZ     calendar calowner      timepart      nextrun   TZO;

   infile flowdefs;

   drop   calowner removed nitems i TZ TZO twopart;

   input;

   if _infile_ =: '<JFFlow>' then

      do;

         jobname = " "; runas = " "; nextrun = " ";  TZO = -1;

         /* Job File Flow definition found. Get FlowName, Owner, and Instance */

         input;  /* eat next line -- length */

         input;  /* should be username:flowname */

         owner    = scan(_infile_,1,':');

         flowname = scan(_infile_,2,':');

         input;  /* eat next line -- length */

         input;  /* should be same as above with %%%%%## instance */

         instance = scan(_infile_,2,'%');

            input;  /* eat next line -- length */

            input;  /* eat next line -- <List> token */

            input;  /* number of items in <List> */

            nitems = input(_infile_||'     ',5.)+3;

            do i = 1 to nitems;

               input;

            end;

            removed = _infile_;

      end;

   else if _infile_ =: 'TimeZone' then

      do;

            input;  /* eat next line -- length */

            input;  /* Time Zone for server */

         TZ = _infile_;

      end;

   else if _infile_ =: 'ServerTZoffset' then

      do;

            input;  /* eat next line -- length */

            input;  /* TZ offset for server */

         TZO = input(_infile_,6.);

      end;

   else if _infile_ =: '<JFTimeInstance>' then

      do;

         /* Job File Trigger instance found. Get Calendar name and time */

         input;  /* eat next line -- length */

         input;  /* should be calendar:hh:mm%dur */

         calendar = scan(_infile_,1,':');

         timepart = scan(_infile_,2,':')||':'||scan(_infile_,3,':%')||'  '||TZ;

         if index(_infile_,'+') then twopart = 1; else twopart = 0;

         input;  /* eat next line -- length */

            input;  /* eat calendar name repeat */

            input;  /* eat next line -- length */

         input;  /* calendar owner */

         calowner = tranwrd(_infile_,'DT_BS_','.\');

         calendar = trim(calendar)||'@'||trim(calowner);

         if twopart then

            do i=1 to 4;

               input; /* eat paired calendar entry */

            end;

         input;  /* eat next line */

         input;  /* eat next line */

         input;  /* eat next line */

         input;  /* next run datetime or flag */

         if _infile_ = "-1" then

            nextrun = "not yet run";

         else

         if length(_infile_) = 10 then

            do;

               i = input(_infile_,10.) + '01JAN1970:00:00:00'dt;  /* times are UNIX/Posix/UTC */

               if TZO = -1 then

                  nextrun = put(i,datetime13.) || " GMT";

               else

                  nextrun = put(i+TZO,datetime13.);

            end;

      end;

   else if _infile_ =: 'JFFileEventDef' then

      nextrun = "(File Event)";

   else if _infile_ =: '<JFJob>' then

      do;

         /* Job Flow Job definition found */

         input;  /* eat next line -- length */

         input;  /* eat next line -- "lsf" */

         input;  /* eat next line -- length */

         input;  /* should be username:flowname:jobname{metaid} */

         jobname = scan(_infile_,3,':{');

         do until (_infile_ =: '</JFJob>');

            input;

            if _infile_ =: 'SubmissionCmd' then

               do;

                  input;  /* eat next line -- length */

                  input;  /* eat next line -- "bsub" */

                  input;  /* eat next line -- length */

                  input;  /* eat next line -- "UserName" */

                  input;  /* eat next line -- length */

                  input;  /* should be RunAs username */

                  runas = _infile_;

               end;

         end;

          /* Write out every job within a flow */

         if removed = "F" then output scheduled;

         else output adhoc;

      end;

run;

filename flowdefs;

 

proc sort data=scheduled;  by owner flowname descending instance jobname; run;

data schedules;

   set scheduled;

   length firstinst $ 8;

   retain firstinst;

   by owner flowname descending instance jobname;

   if first.flowname then firstinst = instance;

   if instance = firstinst;

   drop instance firstinst;

run;

Title 'Output of Scheduled Flows from GetFlowDefinitions';

proc print data=schedules noobs uniform;

by owner;  id owner flowname;  var calendar timepart nextrun jobname runas;

run;

 

/* Don't really need this unless you want to see flows that have been removed

   or were run as "Run Once" (Ad Hoc) and so are never kept as scheduled

 

proc sort data=adhoc;  by owner flowname descending instance jobname; run;

data adhocs;

   set adhoc;

   length firstinst $ 8;

   retain firstinst;

   by owner flowname descending instance jobname;

   if first.flowname then firstinst = instance;

   if instance = firstinst;

   drop instance firstinst;

run;

Title 'Output of Removed or Ad Hoc Flows from GetFlowDefinitions';

proc print data=adhocs noobs uniform;

by owner;  id owner flowname;  var calendar timepart nextrun jobname runas;

run;

   *** end of comment removing Ad Hoc flow reporting */

gwootton
SAS Super FREQ

This program may be helpful:

https://github.com/sascommunities/technical-support-code/blob/main/usage/administration/sas-metadata...

--
Greg Wootton | Principal Systems Technical Support Engineer
Nivas88
Calcite | Level 5

Thank you so much 🙂

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

Get Started with SAS Information Catalog in SAS Viya

SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 410 views
  • 0 likes
  • 3 in conversation