Filename statement in a Macro

[wbsjd]

Hello everyone

The following SAS code which is to move a xlsx file to a zip file works perfectly,

filename xfile "y\test.xlsx" recfm=n;
filename zfile zip "y\archive\Processed.zip" member="test.xlsx" recfm=n;
data _null_;
  rc=fcopy('xfile','zfile');
  put rc=;
  rc=fdelete('xfile')
run;

However, there are multiple xlsx files in the same directory need to be moved to the zip file. I put the above code in a Macro, but the filename statement not working in a Macro.

%macro zip (file_name=);
filename xfile "y\&file_name..xlsx" recfm=n;
filename zfile zip "y\archive\Processed.zip" member="&file_name..xlsx" recfm=n;
data _null_;
rc=fcopy('xfile','zfile');
put rc=;
rc=fdelete('xfile');
run;
%mend;

data _null_;
set files;
call execute('%nrstr(%zip(file_name='||strip(file_name)||'))'); 
run;

Can you please advise how to modify the second SAS code. 

Thank you in advance

[SASKiwi]

Try calling your macro as one statement to get it right:

%zip(file_name=MyExcelFile); 

If that works then try the CALL EXECUTE version.

[wbsjd]

Hello,

I tried your suggest, it works for
%zip(file_name=MyExcelFile);

However, once I use Call Execute, it's not working...

[SASKiwi]

Try this. Looks like you are missing a semicolon between macro calls.

call execute('%nrstr(%zip(file_name='||strip(file_name)||');)');

[wbsjd]

It still doesn't work...

[ballardw]

---

@wbsjd wrote:
Hello,

I tried your suggest, it works for
%zip(file_name=MyExcelFile);

However, once I use Call Execute, it's not working...

---

"It's not working" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

If macro code is involved set OPTIONS MPRINT; prior to running the code and then follow the above about LOG contents.

[wbsjd]

If I run the following code, the 4 xlsx files will be added to the Zip files

%zip(file_name=test); 
%zip(file_name=test1); 
%zip(file_name=test2); 
%zip(file_name=test3); 

The SAS Log for the above code  is 

If I run the code, no xlsx files is moved to the zip file

data _null_;
  set files;
  call execute(cats('%nrstr(%zip)(file_name=%nrstr(%nrstr(',file_name,')))' ));
run;
or 
data _null_;
set files;
call execute('%nrstr(%zip(file_name='||strip(file_name)||'))');run;

The SAS Log is 

I really don't know what's the issue

[Tom]

---

@wbsjd wrote:
Hello,

I tried your suggest, it works for
%zip(file_name=MyExcelFile);

However, once I use Call Execute, it's not working...

---

I suspect it is a timing issue.  Try running two or more macro calls without the data step.  Do the second and third work?  Which steps are failing? The filename statements?  The FCOPY() function call? The FDELETE() function call?   Check the return codes after each step.  

I suspect that SAS is having a hard time reusing the XFILE fileref because the operating system has not really finished with the deletion or the copy.

Or perhaps SAS is having a hard time re-opening the ZIP file to add new files.  Not clear how it does that but I would not be surprised if it would help to add some delays between the steps to let it finish updating the ZIP file on the disk before trying to add another entry into it.

[wbsjd]

It works for %zip(file_name=MyExcelFile); But still not working for Call Execute.

When I run %zip(file_name=MyExcelFile); The log says rc=0. However, If I run Call Execute, rc=20006.

I have no clue why this is

[SASKiwi]

It can be tricky to get CALL EXECUTE to behave. I've found constructing the EXEC statement separately helps:

data _null_;
  set files;
  exec_stmt = '%zip(file_name=' !! strip(file_name) !!');';
  call execute('%nrstr(' !! exec_stmt !! ')');
run;

[wbsjd]

I tried your code, and also other Call Execute code. The xlsx files are  still not successfully moved to the zip file. The log for your code and the following code is roughly the same....

Call Execute provided by others:
data _null_;
  set files;
  call execute(cats('%nrstr(%zip)(file_name=%nrstr(%nrstr(',file_name,')))' ));
run;

[Tom]

I would not wrap %NRSTR() around the whole command.  Just the macro name.  I would not add quotes into the MIDDLE of the filename by adding them into the macro parameter value.  If your file names might strange characters like commas that could confuse the macro processor you might need to add another level of %NRSTR() so that the result is that CALL EXECUTE() pushes a string like:

%zip(filename=%nrstr(my file name with a comma, in it))

onto the stack to run after the macro finishes.

data _null_;
  set files;
  call execute(cats('%nrstr(%zip)(file_name=%nrstr(%nrstr(',file_name,')))' ));
run;

But the real answer is to NOT use the FILENAME statement or a macro. 

Instead use the FILENAME() function. Then you don't need to do any code generation at all.  Just a single data step to process the whole input dataset with the filenames.

Something like (play with the arguments to the FILENAME() function to get it right).  You also probably need to figure out what return code from FDELETE() means it worked. And possibly what return codes for the other function calls mean they worked.

data _null_;
  set files;
  rc1=filename('xfile',cats('y\',file_name,'.xlsx'),,'recfm=n');
  rc2=filename('zfile','y\archive\Processed.zip','zip'
              ,catx(' ',cats('member=',quote(cats(file_name,'.xlsx'))),'recfm=n'));
  rc3=fcopy('xfile','zfile');
  if rc3=XXXX then do;
      * It worked ;
      rc4=fdelete('xfile');
  end;
  rc5=filename('xfile');
  rc6=filename('zfile');
run;

[wbsjd]

When I run the following code all together, all 4 xlsx files are moved to the zip file

%zip(file_name=test); 
%zip(file_name=test1); 
%zip(file_name=test2); 
%zip(file_name=test3); 

The SAS Log is like

When I run one of the following code, no file is moved to the zip file, and the SAS log for the following code is almost the same

data _null_;
  set files;
  call execute(cats('%nrstr(%zip)(file_name=%nrstr(%nrstr(',file_name,')))' )); run;
or
data _null_;
  set files;
  exec_stmt = '%zip(file_name=' !! strip(file_name) !!');';
  call execute('%nrstr(' !! exec_stmt !! ')'); run;
or
data _null_;
set files;
call execute('%nrstr(%zip(file_name='||strip(file_name)||'))');run;

The SAS Log show no error and is like this:

I really don't know what's the issue.

[Tom]

Posting photographs of text makes it harder to respond.

The two are not trying to move the same files.

The manual macro calls did not include a .xlsx extension on the name being passed to the macro.

What are the names of the files?

test ?

or test.xlsx

or test.xlsx.xlsx ?

[wbsjd]

Hi 

%macro zip (file_name=);
filename xfile "y\&file_name..xlsx" recfm=n;
filename zfile zip "y\archive\Processed.zip" member="&file_name..xlsx" recfm=n;
data _null_;
rc=fcopy('xfile','zfile');
put rc=;
rc=fdelete('xfile');
run;
%mend;

%zip(file_name=test); 
%zip(file_name=test1); 
%zip(file_name=test2); 
%zip(file_name=test3); 

The .xlsx is already in the Macro, the name of the file is test.xlsx