SAS DI Job which unzips a file

[vfarmak]

Dear Community,

I am facing the following problem: given a specific folder and zip filename, I need to unzip the file using Data Integration Studio.

In order to achieve this, I have created the following test flow as displayed below.

In the first component I have created a dataset via User Written component with three attributes:

1. The path of the file in which the zip file is included
2. The filename along with the path
3. And the zip file which is basically only the name of the zip file

The code for your convenience is as follows:

DATA PARAMS;
	FILEPATH = 'D:\My_Input';
	FILENAME = 'D:\My_Input\My_File_V2_20210201.zip';
	ZIP = SCAN(FILENAME, 3, '\');
RUN;

DATA &_OUTPUT1;
	SET WORK.PARAMS;
RUN;

Then I try to unzip the file via the "X" command as follows:

1. I create two macro variable and I bind the values of the dataset
2. I write the output in the console
3. I perform the unzip command, just as I would normally do via the command prompt

DATA _null_;
	set &_INPUT1;
	call symputx('pFilepath',strip(Filepath));
	call symputx('pZipname',strip(ZIP));
RUN;

%put &pZipname;
%put %sysfunc(length(&pZipname));

x "cd &pFilepath";
x ' "c:\Program Files\7-Zip\7z.exe" x &pZipname ';

What I have noticed is that when I hardcoded the values and execute the flow, the unzip mechanism works.

When I try to execute the same with the macro variables it fails.

For your convenience below you will find the hardcoded script which works.

x "cd D:\My_Input";
x '"c:\Program Files\7-Zip\7z.exe" x My_File_V2_20210201.zip';

Any suggestion, is highly appreciated.

Best Regards,

Vasileios

[Tom · Accepted solution]

I find you get much better access to error messages if you use the PIPE filename engine to run code instead of just calling the X or SYSTEM() commands.

So build up a string that has the command you want to run and pass that string to the INFILE statement using the FILEVAR= option.

What command do you need to run?  Does the 7z.exe command allow you to tell it where to right the files?  If not then you will need to change your current directory.  Unless Windows has changed how the CD command works sometime the last 20+ years you will first have to make D the default disk before changing directory to a sub directory on that disk.

So it looks like you need to run this Windows command:

D: ; cd "D:\My_Input" ; "c:\Program Files\7-Zip\7z.exe" x "D:\My_Input\My_File_V2_20210201.zip"

So try something like this:

data _null_;
  set &_INPUT1;
  length cmd $600;
  cmd=catx(' ; '
          ,scan(filepath,1,'\')
          ,catx(' ','cd',quote(trim(filepath)))
          ,catx(' ',quote("c:\Program Files\7-Zip\7z.exe"),'x',quote(trim(filename)))
  );
  infile cmd pipe filevar=cmd end=eof;
  do while (not eof);
    input;
    put _infile_;
  end;
run;

But it look like the -o option to 7zip should let you tell it the target path in the one command.

  cmd=catx(' '
          ,quote("c:\Program Files\7-Zip\7z.exe")
          ,'x',quote(trim(filename))
          ,cats('-o',quote(trim(filepath)))
  );

[vfarmak]

I have followed your solution and now I can see from the logs what is being executed.

However, although the command is syntactical OK (tested in Windows Command Prompt and unzips the files), when executed in SAS DI it does not work.

Please find below the log from SAS DI.

NOTE: The infile CMD is:
      Unnamed Pipe Access Device,
      PROCESS=D: ; cd "D:\VF_Input" ; "c:\Program Files\7-Zip\7z.exe" x MY_DELTA_V2_20210201.zip -oD:\VF_Input,
      RECFM=V,LRECL=32767

NOTE: 0 records were read from the infile CMD.
NOTE: There were 1 observations read from the data set WORK.W91LV79I.
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2021-10-18T12:49:56,405+03:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 20025344| _DISARM| 15040512| _DISARM| 10| _DISARM| 10| _DISARM| 299520| _DISARM| 12425808| _DISARM| 0.000000| 
      _DISARM| 0.042000| _DISARM| 1950169796.363000| _DISARM| 1950169796.405000| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.00 seconds

Does SAS comes with a simple command so as to unzip the file?

I do not want to read the contents from the zip file. I need only to unzip the archive.

Thank you in advance.

Vasilios

[vfarmak]

Your solution seems to work with one detail:

When using data step so as to perform a windows operation, it does not work.

Despite this fact, I have used the code snippet you provided me as you can see below:

/* Bind Input Variables to Local SAS Macro Variables */
DATA _null_;
	set &_INPUT1;
	call symputx('pFilepath',strip(Filepath));
	call symputx('pZipname',strip(ZIP));
	call symputx('pDrive',scan(Filepath,1,'\'));
RUN;

/* Output the Variables to Log */
%put &pFilepath;
%put &pZipname;


/* Using sysexec to execute unzip command */
%sysexec &pDrive;
%sysexec cd &pFilepath;
%sysexec "C:\Program Files\7-Zip\7z.exe" x &pZipname;

The datastep provides with the filevar approach provides a good validation of the command I tried to enter and indeed I had some problems.

When it comes to real execution, the approach yield no results.

Therefore, I "ported" your data step code in %sysexec and it Worked!

Thank you!

[vfarmak]

@Tom 

I am experiencing the following error:

Stderr output:
'c:\Program' is not recognized as an internal or external command,
operable program or batch file.

It seems that the cmd inside the datastep sees the empty space in the 'Program Files' path and the command is not executed.

I saw that you used the quote() function.

Do you have any suggestions?

Thank you in advance,

Vasilios

[Tom]

Is this the same question or a new one?  What is the actual command you ran?

You need to enclose the filenames with spaces in quotes for Windows/DOS to see them as one token.

So you want to run something like

"c:\program files\somecommand"  options

Whether or not QUOTE() helps create that depends on what exactly you are doing.

[vfarmak]

Hi @Tom 

It's the same command.

However I made it work.

Here is the code:

%let proc = ;

DATA _NULL_;
  length cmd $6000.;
  cmd=catx(' '
          ,quote("C:\Program Files\7-Zip\7z.exe")
          ,'x', quote(trim("&tfile"))
          ,cats('-o',quote(trim("&tpath")))
  );
  call symputx('proc', cmd);

run;

%sysexec &proc;

I used the outcome of your code stored in the variable cmd and passed it to a macro variable named proc.

Then I used the %sysexec command.

I know that you cannot actually monitor the outcome of the process, since I sense that SAS DI simply uses the sysexec macro command to pass an instruction to the operating system.

I tried also to use the first example you suggested and experiment a little bit with the Return Code Check component, so as to catch the syserr exception with no luck.

Although I made it work, still it is not a very good solution...

[Tom]

You probably want to add the option that stops it from displaying the progress information since there is no user to look at it.

https://sevenzip.osdn.jp/chm/cmdline/switches/index.htm

-bd	Disable progress indicator

[AllanBowe]

You may be able to do this without X command - here is a "pure sas" unzip macro:  https://core.sasjs.io/mp__unzip_8sas.html

[Patrick]

Have you considered using the filename zip engine?

Do you need to unzip the archive or do you just need to read a file in a zip archive. If it's the latter then have a look at this blog: https://blogs.sas.com/content/sasdummy/2015/05/11/using-filename-zip-to-unzip-and-read-data-files-in... 

More here: https://blogs.sas.com/content/sasdummy/tag/filename-zip/ 

[vfarmak]

I just need to unzip the archive.

Although the command runs perfectly in command prompt, when I execute the code via SAS DI, it does not unzip the archive.