I want to list all libraries used per job in DI studio

[jakarman]

So what you want is an Impact analyses / reverse impact analyses for all tables?

SAS(R) Data Integration Studio 4.4: User's Guide  being enhanced SAS(R) Data Integration Studio 4.7: User's Guide

That can be done analyzing the metadata as Patrick suggested. The metdata is rather a complicated structure. SAS(R) 9.4 Metadata Model: Reference (just the tables, I am missing associations to transforms).

Your method of brute force code analyzing is more easy to understand.  The list tables-libraries must be not the biggest problem. 

It has  the disadvantage you are needing to recognize sure a library-table combination found in some code/dataset.  Is this the question?
Having two tables with all information it will become a normal table-lookup -reporting question.  

[Filipvdr]

Thanks for your replies.

I have tried your suggestion Patrick but it seems it throws out the table does not include the big parts which are commented.

[Patrick]

DI 4.21 is an older version but I would have thought that DIS already generated such a header in this version. Do you have it in your code and you're simply not reading it into your data? Or what exactly is the problem you're facing?

[LinusH]

Perhaps we are back to my original suggestion to use SAS Code Analyzer (PROC SCAPROC)....?

It is available in 9.2.

Base SAS(R) 9.2 Procedures Guide

[jakarman]

Linus I always placed SCAPROC for optimizing code in smaller parts to do parallel execution. DI is generating code. I do not see a match.
Still it could be an option as Filipvdr did explain his ultimate goal. At the moment it looks to be an inventarization / impact analyses.

For the (statistic) code analyses.

The code generations is influenced by options in DI. I am not sure whether those descriptive headers are possible.
Filipvdr/Patrcik on the track.

When not, more code must be read with some assumptions on naming conventions. When there are a limited set transformations used it could be workable. When a lot of code has changed by handcraft. It is a battle of outsmarting coders work.   

[LinusH]

The "match" is that the deployed DI code can by analyzed, and the PROC SCAPROC output is probably easier to parse than the deployed code.

[LinusH]

Another advantage is if you have been cutting corners in your DI job, by accessing tables in user written code which not reflected in metadata, these can't be found in transformations headers. In that respect should SCAPROC be more fool proof.

[jakarman]

The disadvantage is you to run every job as a command specifying scaproc. It does run-time analyses not static on code. http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a003199745.htm

It can detect dead-code but that could also error recovery or an other special case (still relevant).

[Filipvdr]

Thanks all for your help.

data all_data;

set TEMP.TABLE_PGM_CONTENTS_COMMENTS;

run;

proc sort data=all_data; by pgm_sas line_nr; run;

proc sql;

create table allLibnames as select distinct libname from sashelp.vlibnam where libname ne "WORK";

run;

data allLibnames;

set allLibnames;

lengthlib = length(libname);

run;

proc sort data=allLibnames; by lengthlib; run;

  proc sql;

  select strip(libname) into:all_libs SEPARATED BY "#"  from alllibnames;

quit;

%macro loopLibname;

%let count = 1;

data Result;

format library $8.;

set all_data;

%let lib=%scan(&all_libs,&count,%STR(#));

%do %while (&lib ne);

if index(upcase(pgm_line) , "&lib")

and index(upcase(pgm_line) , "*")

and not index(upcase(pgm_line) , "/*") 

and not index(upcase(pgm_line) , "(*)") 

and not index(upcase(pgm_line) , "JOB") 

and not index(upcase(pgm_line) , "SCD2") 

and not index(upcase(pgm_line) , "/") 

then library  = "&lib";

%let count=%eval(&count+1);

%let lib=%scan(&all_libs,&count,%STR(#));

%end;

if library ne "";

run;

%mend;

%looplibname;

proc sort data=result nodupkey; by  pgm_sas library; run;

data TEMP.LIBRARIES_PER_JOB;

set result(keep=pgm_sas library);

run;

[JasonBCoding]

Came across to this thread whilst I was doing a bit of research on the similar topic.

I actually wrote a SAS code which scans the all the SAS files in order to find all the permanent libraries and tables/data used within the SAS code. This could be used in the DI Studio job too as long as the code is generated and saved a .sas file.

Feedback will be appreciated. Have fun!

p/s: max size of the file for uploading is 1k? paste the code as below:

*** ----------------------------------------------------------------------------- ***;

*** This switch is to include scanning the library assigned to a macro variable  ***;

*** For example: %LET thisLib = ABC;  ***;

*** ----------------------------------------------------------------------------- ***;

%LET includeMaroLib = Y;

*** ------------------------------------------------------------------------- ***;

*** Note: The following DATA step may not apply to your environment  ***;

*** ------------------------------------------------------------------------- ***;

DATA _NULL_;

  _CLIENTUSERID1 = STRIP(tranwrd(&_CLIENTUSERID, "'", ""));

  CALL SYMPUT("_CLIENTUSERID1", COMPRESS(_CLIENTUSERID1) );

RUN;

*** ------------------------------------------------------------------------- ***;

*** Note: Modify this to your user folder    ***;

*** ------------------------------------------------------------------------- ***;

%LET outputFolder = H:\users\&_CLIENTUSERID1\;

%PUT --- &_CLIENTUSERID1 ---;

%PUT --- &outputFolder ---;

*** find out all the .sas file in the directory ***;

%macro searchLibraries(dir,ext);                                                                                                                 

  DATA findSASJobs;

  ATTRIB filename LENGTH = $100;

  ATTRIB targetFolder LENGTH = $100;

   %let filrf=mydir;                                                                                                                     

   /* Assigns the fileref of mydir to the directory and opens the directory */                                                                   

   %let rc=%sysfunc(filename(filrf,&dir));                                                                                               

   %let did=%sysfunc(dopen(&filrf));                                                                                                        

   /* Returns the number of members in the directory */                                                                  

   %let memcnt=%sysfunc(dnum(&did));                                                                                                     

    /* Loops through entire directory */                                                                                                 

    %do i = 1 %to &memcnt;                                                                                                               

      /* Returns the extension from each file */                                                                                                                                   

      %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);                                                                                  

      /* Checks to see if file contains an extension */                                                                                    

      %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&ext) %then %do;                                                                 

      /* Checks to see if the extension matches the parameter value */                                                                     

      /* If condition is true prints the full name to the log       */                                                                     

       %if (%superq(ext) ne and %qupcase(&name) = %qupcase(&ext)) or                                                                      

          (%superq(ext) = and %superq(name) ne) %then %do;                                                                                    

          %put %qsysfunc(dread(&did,&i));                 

  %let filename =%sysfunc(dread(&did,&i));

  filename = "&filename";

  targetFolder = "&dir";

  OUTPUT;

       %end;                                                                              

      %end;                                                                                                                              

    %end;                                                                                                                                

   /* Closes the directory */                                                                                                           

   %let rc=%sysfunc(dclose(&did));                                                                                                       

  RUN;                                                                                                                                       

  *** create the macro variables for the file name ***;

  DATA findSASJobs1;

  SET findSASJobs END=EOF;

     CALL symput(compress("filename"||PUT(_N_,3.)),filename);;

  IF EOF THEN CALL SYMPUT("nFilename", _N_);

  RUN;

  %DO ii = 1 %TO &nFilename;

  *** find out all the libraries within a particular code ***;

  FILENAME textFile "&dir\&&filename&ii";

  DATA SAScodesⅈ

  INFILE textFile MISSOVER TRUNCOVER DELIMITER=',' LRECL = 320;

  ATTRIB textRow LENGTH = $500;

  ATTRIB SASfilename length = $100;

  ATTRIB cleanLibName length = $8;

  ATTRIB targetFolder LENGTH = $100;

  INPUT textRow $ ;

  IF INDEX(UPCASE(textRow), "LIBNAME") > 0 OR INDEX(UPCASE(textRow), "LIBALLOC") > 0 THEN DO;

  SASfilename = "&&filename&ii";

  targetFolder = "&dir";

  **** strip out the noise to get accurate library name ****;

  IF INDEX(UPCASE(textRow), "LIBNAME") > 0 THEN DO;

  cleanLibName = UPCASE(STRIP(SCAN(textRow, 2, " ")));

  END;

  IF INDEX(UPCASE(textRow), "LIBALLOC") > 0 THEN DO;

  cleanLibName = UPCASE(STRIP(SCAN(textRow, 2)));

  END;

  OUTPUT;

  END;

  RUN;

  %if %sysfunc(exist(work.LibrarySearch)) = 1 %then %do;

  *** append if exists ***;

  PROC APPEND BASE = LibrarySearch

  DATA = SAScodes&ii FORCE;

  run;

  %PUT -----------> &ii = &&filename&ii hellohere1;

  %END;

  %ELSE %if %sysfunc(exist(work.LibrarySearch)) = 0 %then %do;

  *** create new if not exists ***;

  DATA LibrarySearch;

  SET SAScodesⅈ

  run;

  %PUT -----------> &ii = &&filename&ii hellohere2;

  %END;

  *** ------------------------------------------------------------------------- ***;

  *** remove duplicate library ***;

  *** ------------------------------------------------------------------------- ***;

  *** remove the duplicate based on the cleanLibName ***;

  proc sort nodupkey data = SAScodes&ii out = clean_SAScodesⅈ

  by cleanLibName;

  run;

  *** create the macro variables for the library name ***;

  DATA _NULL_;

  SET clean_SAScodes&ii END=EOF;

     CALL symput(compress("cleanLibName"||PUT(_N_,3.)),compress(cleanLibName));;

  IF EOF THEN CALL SYMPUT("nCleanLibName", _N_);

  RUN;

  %PUT check this out --------------------> %CMPRES(&&cleanLibName1..);

  %DO kk = 1 %TO &nCleanLibName;

  %PUT check this out --------------------> %CMPRES(&&cleanLibName&kk...);

  %END;

  *** ------------------------------------------------------------------------- ***;

  *** ------------------------------------------------------------------------- ***;

  *** search for all the tables used by the relevant libraries ***;

  *** ------------------------------------------------------------------------- ***;

  *** ------------------------------------------------------------------------- ***;

  *** find out all the libraries within a particular code ***;

  FILENAME textFile "&dir\&&filename&ii";

  DATA SASMacroLibraryⅈ

  INFILE textFile MISSOVER TRUNCOVER DELIMITER=',' LRECL = 320;

  ATTRIB textRow LENGTH = $500;

  ATTRIB SASfilename length = $100;

  ATTRIB MacroLibName length = $20;

  ATTRIB targetFolder LENGTH = $100;

  INPUT textRow $ ;

  *** output the data ***;

  %DO kk = 1 %TO &nCleanLibName;

  IF INDEX(UPCASE(textRow), "%CMPRES(&&cleanLibName&kk)") > 0 AND INDEX(UPCASE(textRow), %NRSTR('%LET')) > 0 THEN DO;

  MacroLibName = CATS('&', STRIP(SCAN(UPCASE(textRow), 2, " ")), '.' ) ;

  SASfilename = "&&filename&ii";

  targetFolder = "&dir";

  OUTPUT;

  END;

  %END;

  RUN;

  DATA bothSASLibⅈ

  SET clean_SAScodes&ii

  SASMacroLibrary&ii (RENAME = (MacroLibName = cleanLibName))

  END=EOF;

      CALL symput(compress("allLibName"||PUT(_N_,3.)),compress(cleanLibName));;

  IF EOF THEN CALL SYMPUT("nAllLibName", _N_);

  RUN;

  %PUT Piping this SAS Code --------------------> &&filenameⅈ

  %DO jj = 1 %TO &nAllLibName;

  %PUT check this out all library --------------------> %CMPRES(&&AllLibName&jj...);

  %END;

  %if %sysfunc(exist(work.AllLibrarySearch)) = 1 %then %do;

  *** append if exists ***;

  PROC APPEND BASE = AllLibrarySearch

  DATA = bothSASLib&ii FORCE;

  run;

  %PUT -----------> &ii = bothSASLib&ii ;

  %END;

  %ELSE %if %sysfunc(exist(work.AllLibrarySearch)) = 0 %then %do;

  *** create new if not exists ***;

  DATA AllLibrarySearch;

  SET bothSASLibⅈ

  run;

  %PUT -----------> &ii = bothSASLib&ii ;

  %END;

  *** ------------------------------------------------------------------------- ***;

  *** ------------------------------------------------------------------------- ***;

  *** search for all the tables used by the relevant libraries ***;

  *** ------------------------------------------------------------------------- ***;

  *** ------------------------------------------------------------------------- ***;

  *** find out all the libraries within a particular code ***;

  FILENAME textFile "&dir\&&filename&ii";

  DATA SASTablesⅈ

  INFILE textFile MISSOVER TRUNCOVER DELIMITER=',' LRECL = 320;

  ATTRIB textRow LENGTH = $500;

  ATTRIB SASfilename length = $100;

  ATTRIB cleanLibName length = $8;

  ATTRIB cleanTableName length = $30;

  ATTRIB targetFolder LENGTH = $100;

  INPUT textRow $ ;

  %IF %UPCASE(&includeMaroLib) = N %THEN %DO;

  *** version WITHOUT the macro library name ***;

  IF INDEX(UPCASE(textRow), "%NRBQUOTE(%CMPRES(&&cleanLibName1..))") > 0

  %DO kk = 2 %TO &nCleanLibName;

  OR INDEX(UPCASE(textRow), "%NRBQUOTE(%CMPRES(&&cleanLibName&kk...))") > 0

  %END; THEN DO;

  *** output the data ***;

  %DO kk = 1 %TO &nCleanLibName;

  IF INDEX(UPCASE(textRow), "%NRBQUOTE(%CMPRES(&&cleanLibName&kk...))") > 0 THEN DO;

  cleanLibName = UPCASE("%NRBQUOTE(&&cleanLibName&kk)");

  cleanTableName = STRIP(SCAN(STRIP(SCAN(STRIP(SCAN(LOWCASE(textRow), 2, ".;(")), 1, '09'x)), 1, " "));

  END;

  %END;

  SASfilename = "&&filename&ii";

  targetFolder = "&dir";

  OUTPUT;

  END;

  %END;

  %ELSE %IF %UPCASE(&includeMaroLib) = Y %THEN %DO;

  *** version that includes the macro library name ***;

  IF INDEX(UPCASE(textRow), "%NRBQUOTE(%CMPRES(&&allLibName1..))") > 0

  %DO jj = 2 %TO &nAllLibName;

  OR INDEX(UPCASE(textRow), "%NRBQUOTE(%CMPRES(&&AllLibName&jj...))") > 0

  %END; THEN DO;

  *** output the data ***;

  %DO jj = 1 %TO &nAllLibName;

  IF INDEX(UPCASE(textRow), "%NRBQUOTE(%CMPRES(&&AllLibName&jj...))") > 0 THEN DO;

  cleanLibName = UPCASE("%NRBQUOTE(&&AllLibName&jj)");

  cleanTableName = STRIP(SCAN(STRIP(SCAN(STRIP(SCAN(LOWCASE(textRow), 2, ".;(")), 1, '09'x)), 1, " "));

  END;

  %END;

  SASfilename = "&&filename&ii";

  targetFolder = "&dir";

  OUTPUT;

  END;

  %END;

  RUN;

  %if %sysfunc(exist(work.AllTablesSearch)) = 1 %then %do;

  *** append if exists ***;

  PROC APPEND BASE = AllTablesSearch

  DATA = SASTables&ii FORCE;

  run;

  %PUT -----------> Appending &ii = &&filename&ii out of &nfilename;

  %END;

  %ELSE %if %sysfunc(exist(work.AllTablesSearch)) = 0 %then %do;

  *** create new if not exists ***;

  DATA AllTablesSearch;

  SET SASTablesⅈ

  RUN;

  %PUT -----------> &ii = &&filename&ii hellohere2;

  %END;

  %END;

  **** remove all the temp tables ***;

  proc datasets nolist library=work ;

  %DO ii = 1 %TO &nFilename;

    delete SAScodesⅈ

    delete SASTablesⅈ

    delete clean_SAScodesⅈ

    delete SASMacroLibraryⅈ

    delete bothSASLibⅈ

  %END;

    delete findSASJobs1;

  run;

%mend searchLibraries;                                                                                                                           

*** ------------------------------------------------------------------------------- ***;

*** ------------------------------------------------------------------------------- ***;

*** ------------------------------ Change here ------------------------------------ ***;

*** Add the folders into macro for searching over here     ***;

*** Parameters for Macro Variables below: Folder location, Type of file to search ***;

*** Note: %NRBQUOTE is important for it to mask special characters   ***;

*** Note: Output files will be in your H:\ folder   ***;

*** ------------------------------------------------------------------------------- ***;

*** ------------------------------------------------------------------------------- ***;

%searchLibraries(%NRBQUOTE(G:\temp), sas);

*** ------------------------------------------------------------------------- ***;

*** Note: Do not edit any of the codes below    ***;

*** ------------------------------------------------------------------------- ***;

proc sort data = LibrarySearch;

  by SASfilename;

run;

proc sort nodupkey data = LibrarySearch out = UniqueLibrary;

  by textRow;

run;

proc sort data = AllTablesSearch;

  by SASfilename cleanLibName cleanTableName ;

run;

proc sort nodupkey data = AllTablesSearch out = UniqueTables;

  by cleanTableName;

run;

proc sort data = UniqueTables;

  by SASfilename cleanLibName cleanTableName ;

run;

%MACRO exportCSV(outputfile);

  PROC EXPORT DATA = &outputfile.

    outfile = "&outputFolder\&outputfile..csv"

   DBMS = CSV

   REPLACE;

  RUN;

%MEND exportCSV;

%exportCSV(LibrarySearch);

%exportCSV(UniqueLibrary);

%exportCSV(AllTablesSearch);

%exportCSV(UniqueTables);

%exportCSV(AllLibrarySearch);

  *** remove the temp tables in the works directory as the steps involve appending data into these temp work tables ***;

  proc datasets nolist library=work ;

    delete LibrarySearch;

    delete UniqueLibrary;

    delete AllTablesSearch;

    delete UniqueTables;

    delete findSASJobs;

    delete AllLibrarySearch;

  run;

[Peter_C]

Could be fun to "peer review" your code

[JasonBCoding]

Yeah, please do! I actually had fun writing it.

Couple of things I had in mind to improve it such as automatically going into the subfolders (can find the code online) and scan multiple library/tables per line instead of just one, but just time poor.

[Peter_C]

I think it's a lot of work (macro code) to acquire a list of separate files, then bring all together  

when statement like

INFILE 'p*th/fil*.sas' truncover filename= fname .....

could bring all results into one data step eliminating a lot of syntax. Notice it supports global chars ( * and ?) not only in the filename but also in a subdirectory name. This means you can infile from all .sas files at a hierarchy level in a "single pass". The FILENAME= option of infile statement tells you the name and path of the file read by the latest INPUT.

Beware using a unix path like xxxxx/*.sas

Instead of letting  that start a comment block, use something like xxxxx/?*

That ? between  / and * saves a lot of debugging 😉

However, I still think that for well implemented DI jobs, metadata analysis might be much more robust ---  See metacoda.com

good luck

peterC

[JasonBCoding]

great ideas and info about those tricks!! Cheers