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;
... View more
