topic Re: Zip/Unzip Macro using VBScript won't overwrite files in SAS Programming

Zip/Unzip Macro using VBScript won't overwrite files

viola — Thu, 11 Oct 2018 18:25:30 GMT

I found a macro presented at SAS Global Forum to help zip/unzip compressed files. I am downloading a zip file daily, extracting the files to a folder, then performing further processing in SAS. The zip file contains the same set of files every time - I need to unzip the files and then overwrite the previous day's files. I'm trying to run my program in batch mode, and keep getting the window that asks me to confirm whether or not I want to replace the files. I need to suppress this window and have the files overwrite automatically. I don't know enough about VBScript to understand where to insert an overwrite option. What modification do I need to make?

%macro SASZip_Lite(zip=, sfdr=, fstyl=%str(*.sas*dat), tfdr=);
  /****************************************************************
  The code posted below is provided "AS IS" with NO WARRANTIES.
  ZIP: directory and file name of zip archive
  SFDR: directory of source files (to be zipped)
  FSTYL: File type of source files; value: *.* as "zip a folder"
  TFDR: Target directory for unzipped files (for unzip)
  *****************************************************************/ 

%local zip sfdr fstyl tfdr vbadir p q mode;

 /* Set up a temporary working folder for VBScript */
    %let vbsdir=c:\MyZi$Dir;

 options noxwait;

 /* To initiate a clean working space */
 %if %sysfunc(fileexist("&vbsdir"))=1 %then %sysexec rd /s/q "&vbsdir";
 %if %index(%upcase(&zip), .ZIP)=0 %then %let zip=&zip..zip;
 %let mode=;

 /* Compress (zip) files */
 %if %length(&sfdr)>0 and (%length(&zip)>0) %then %do;
 	/* Extract directory name of the zip file, if no such folder, generate one */
 	%let q=%sysfunc(tranwrd(&zip, %scan(&zip, -1, %str(\)), %str( )));
 	%let q=%substr(&q, 1, %length(&q)-1);
 	%if %sysfunc(fileexist("&q"))=0 %then %sysexec md "&q";

 	/* Copy all requested files from a validated source folder to a temporary folder,
 		and keep their original time stamps */
 	%if %length(&sfdr)>0 and %sysfunc(fileexist("&sfdr"))=1 %then %do;
 		%let mode=z;
 		%sysexec md "&vbsdir";
 		%if %qupcase(&fstyl)^=%str(*.*) %then %do;
 			%sysexec md "&vbsdir.\temp_zip";
 			%sysexec copy "&sfdr.\&fstyl" "&vbsdir.\temp_zip"; 
 		%end;
 	%end;
%end;
%else %if %length(&tfdr)>0 and %length(&zip)>0 and %sysfunc(fileexist("&zip"))>0
%then %do; /* Unzip files */
  %let mode=u;
  %sysexec md "&vbsdir";
%end; 

%if &mode=z or &mode=u %then %do;
 /* Generate VBScript based on different modes */
 	data _null_;
 		FILE "&vbsdir.\xpzip.vbs";
 		put 'Set ZipArgs = WScript.Arguments';
 		put 'InputFile = ZipArgs(0)';
 		put 'TgtFile = ZipArgs(1)';
 		put 'Set objShell = CreateObject("Shell.Application")';
 		put 'Set source = objShell.NameSpace(InputFile).Items';
 		put 'soucnt = objShell.NameSpace(InputFile).Items.Count';

 		%if &mode=z %then %do;
 			put 'CreateObject("Scripting.FileSystemObject").CreateTextFile(TgtFile,
		 		 True).Write "PK" & Chr(5) & Chr(6) & String(18, Chr(0))';
 			put 'objShell.NameSpace(TgtFile).CopyHere(source)';
 			put 'Do Until objShell.NameSpace(TgtFile).Items.Count = soucnt';
 			put 'wScript.Sleep 3000';
 			put 'Loop';
 		%end;
 		%else put 'objShell.NameSpace(TgtFile).CopyHere(source)'; ;
 		put 'wScript.Sleep 3000';
  	run;

	 /* Run VBScript file for data archiving */
 	%if &mode=z %then %do;

 		%if %qupcase(&fstyl)=%str(*.*) %then %sysexec CScript "&vbsdir.\xpzip.vbs"
			  "&sfdr" "&zip";
 			%else %sysexec CScript "&vbsdir.\xpzip.vbs" "&vbsdir.\temp_zip" "&zip";
 	%end;
 	%else %sysexec CScript "&vbsdir.\xpzip.vbs" "&zip" "&tfdr";
%end;

 /* Clean up */
 %if %sysfunc(fileexist("&vbsdir"))=1 %then %sysexec rd /s/q "&vbsdir";

%mend SASZip_Lite;

Re: Zip/Unzip Macro using VBScript won't overwrite files

Reeza — Thu, 11 Oct 2018 20:08:19 GMT

Are you stuck with this process? What types of files are in the zip file? Text? Sas data sets? What version of SAS do you have?

The reason I ask, is that newer versions of SAS has automated ways of doing this without the need for VBS. If you have the latest version you can read a text file directly from the ZIP file without having to uncompress it.

Re: Zip/Unzip Macro using VBScript won't overwrite files

viola — Thu, 11 Oct 2018 20:22:02 GMT

@Reeza - definitely open to another way if there is one. ZIP contains CSV files. Running SAS 9.4 on Windows 10.

I have another process later on that extracts the names from the CSV files using the filename statement - not sure if that is dependent upon the files being unzipped already, or not. This is the code that needs to follow the unzipping of the file:

filename csvfiles pipe "dir ""&directory\*.csv"" /b";

data csvlist;
	length csvname $100; 
    infile csvfiles length=reclen;
	input csvname $varying100. reclen;
run;

data _null_;
	set csvlist end=end;
	count+1;
	call symputx('path'||put(count,4.-l),cats("&directory\",csvname));
	call symputx('sasname'||put(count,4.-l),scan(csvname,1,'.'));
	if end then call symputx('max',count);
run;

Re: Zip/Unzip Macro using VBScript won't overwrite files

Reeza — Thu, 11 Oct 2018 20:39:40 GMT

This is probably what you want then:

https://blogs.sas.com/content/sasdummy/2015/05/11/using-filename-zip-to-unzip-and-read-data-files-in-sas/

Note that the code is at the bottom of the blog post. For a text file, you can read it directly from the zip file, the documentation has an example, the third one.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lestmtsglobal&docsetTarget=n1dn0f61yfyzton1l2ngsa1clllr.htm&locale=en#p0ev6qrbgvl7p3n148timdyxob68

@viola wrote:

@Reeza - definitely open to another way if there is one. ZIP contains CSV files. Running SAS 9.4 on Windows 10.

I have another process later on that extracts the names from the CSV files using the filename statement - not sure if that is dependent upon the files being unzipped already, or not. This is the code that needs to follow the unzipping of the file:
filename csvfiles pipe "dir ""&directory\*.csv"" /b";

data csvlist;
	length csvname $100; 
    infile csvfiles length=reclen;
	input csvname $varying100. reclen;
run;

data _null_;
	set csvlist end=end;
	count+1;
	call symputx('path'||put(count,4.-l),cats("&directory\",csvname));
	call symputx('sasname'||put(count,4.-l),scan(csvname,1,'.'));
	if end then call symputx('max',count);
run;