Automate Zip files Using MACRO By Arjun Shrestha

Automate & Zip files Using MACRO By: Arjun Shrestha

Overview • Requirement • Architectural design • Implementation and code Objective of this presentation is to demonstrate automating zipping process using advance SAS MACRO. I will discuss translating requirements into architectural design and SAS macro approach to get desired outcome

Requirement & Architectural Design - Pickup files based on dates modified from different folder and dump into zipped folder

Code PROC PRINTTO; RUN; %global FILE_NAME; %global zipped_folder_name; %global numoflocation; %global numofftozip; %LET path = EXCEL_FILE_LOCATION; %LET log. PATH = LOG_FILE_LOCATION; DATA _NULL_; RUN; CALL SYMPUT('LOGTIME', STRIP(COMPRESS(PUT(DATE(), YYMMDD 10. ), '-'))||'_'||STRIP(COMPRESS(PUT(TIME(), TOD 9. ), ': ')) ); PROC PRINTTO LOG="&log. PATHSAS_LOGzipp_&LOGTIME. . LOG"; RUN; DATA INPUT_DATA; CALL SYMPUT('FNAME', '''' || 'DIR "'|| "&PATH"|| '" /B' || ''''); RUN; %PUT FNAME = &FNAME;

Code Continue… filename DIRLIST pipe &FNAME; %MACRO FIND_EXCEL_FILE; %LET FILE_NAME = ''; %LET BOL_FILEFOUND = 0; data dirlist ; infile DIRLIST lrecl=200 truncover; input file_name $200. ; IF INDEX(upcase(file_name), "XLSX") ne 0 THEN DO; CALL SYMPUT('FILE_NAME', FILE_NAME); call symput('zipped_folder_name', scan(FILE_NAME, 1, '. ')); OUTPUT; END; run; %LET BOL_FILEFOUND = &SYSNOBS; %PUT BOL_FILEFOUND = &BOL_FILEFOUND. ; %LET FILE_NAME = &FILE_NAME; %PUT FILE_NAME = &FILE_NAME; %put zipped_folder_name = &zipped_folder_name. ; %MEND FIND_EXCEL_FILE; %FIND_EXCEL_FILE;

Code Continue… %MACRO READEXCEL; PROC IMPORT DATAFILE = %IF %LENGTH("&FILE_NAME. ") > 3 %THEN %DO; %END; %ELSE %DO; "&PATH&FILE_NAME. " "&PATH&HARDCODED_FILE_NAME. " %END; DBMS = XLSX OUT = inputfolder(where=(input_file_location is not null)) REPLACE; SHEET = sheet 1; RUN; %MEND READEXCEL; %READEXCEL; proc sql; create table DUMMY as select input_file_location from inputfolder; quit; %let numoflocation = &sqlobs. ; %put numoflocation = &numoflocation. ;

Code Continue… proc sql; select input_file_location , start_date , end_date , input_file_type , output_file_location , output_zipped_folder_name into : input_file_location 1 -: input_file_location&numoflocation. , : start_dt 1 -: start_dt&numoflocation. , : end_dt 1 -: end_dt&numoflocation. , : input_file_type 1 -: input_file_type&numoflocation. , : output_file_location 1 -: output_file_location&numoflocation. , : output_zipped_folder_name 1 -: output_zipped_folder_name&numoflocation. from inputfolder; quit; filename newfile "&&output_file_location&numoflocation. . &zipped_folder_name. . zip"; data _null_; if (fexist('newfile')) then rc = fdelete('newfile'); run; filename newfile clear;

Code Continue… options mprint mlogic; %macro RUN_PACKAGE; ods package(&zipped_folder_name. ) open nopf; %do i=1 %to &numoflocation. ; %macro zipfiles(input_file_location= , output_file_location= , zipped_folder_name= , start_dt= , end_dt= , input_file_type=); %let input_file_location = &input_file_location. ; %let output_file_location = &output_file_location. ; %let start_dt = &start_dt. ; %let end_dt = &end_dt. ; %let input_file_type=&input_file_type; DATA run_mon_&i. ; CALL SYMPUT('FNAME'||strip(put(&i. , 4. )), "&input_file_location. " || '" /B' || ''''); RUN; %PUT FNAME&i. = &&FNAME&i. . ; filename DIRLIST pipe &&FNAME&i. . ; '''' || 'DIR "'||

Code Continue… data test&i. ; infile dirlist pad lrecl=250; input @1 filename $250. ; rcs = filename("fileref", "&input_file_location. " || trim(filename)); msgs = sysmsg(); fid=fopen('fileref'); Bytes=finfo(fid, 'File Size (bytes)'); crdate=finfo(fid, 'Create Time'); moddate=finfo(fid, 'Last Modified'); moddate_check=compress(put(input(substr(moddate, 1, 9), date 9. ), yymmdd 10. ), '-'); rc = fclose(fid); rcc = filename("fileref"); if moddate_check ge "&start_dt" and moddate_check le "&end_dt. " and INDEX(upcase(filename), upcase("&input_file_type. ")) ne 0 then do; call execute (catx (' ', 'ods package(&zipped_folder_name. )', 'add file=', quote("&input_file_location. " || trim(filename)), '; ' ) ); end; run; %mend zipfiles;

Code Continue… %zipfiles(input_file_location=&&input_file_location&i. . , output_file_location=&&output_file_location&i. . , zipped_folder_name=&zipped_folder_name. , start_dt=&&start_dt&i. . , end_dt=&&end_dt&i. . , input_file_type=&&input_file_type&i. . ); %end; ods package(&zipped_folder_name. ) publish archive properties(archive_name="&zipped_folder_name. . zip" archive_path="&&output_file_location&numoflocation. . "); ods package(&zipped_folder_name. ) close; %mend RUN_PACKAGE; %RUN_PACKAGE;

Questions ?