topic Re: Filename statement in a Macro in SAS Programming

Filename statement in a Macro

wbsjd — Thu, 22 Jun 2023 21:53:52 GMT

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

Re: Filename statement in a Macro

SASKiwi — Thu, 22 Jun 2023 22:41:07 GMT

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

%zip(file_name=MyExcelFile);

If that works then try the CALL EXECUTE version.

Re: Filename statement in a Macro

wbsjd — Thu, 22 Jun 2023 23:22:01 GMT

Hello,

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

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

Re: Filename statement in a Macro

wbsjd — Thu, 22 Jun 2023 23:25:46 GMT

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

Re: Filename statement in a Macro

SASKiwi — Thu, 22 Jun 2023 23:31:31 GMT

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

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

Re: Filename statement in a Macro

wbsjd — Thu, 22 Jun 2023 23:44:41 GMT

It still doesn't work...

Re: Filename statement in a Macro

SASKiwi — Fri, 23 Jun 2023 00:48:10 GMT

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;

Re: Filename statement in a Macro

Tom — Fri, 23 Jun 2023 04:07:58 GMT

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;

Re: Filename statement in a Macro

ballardw — Fri, 23 Jun 2023 10:11:05 GMT

@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-data-AKA-generate/ta-p/258712 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.

Re: Filename statement in a Macro

Tom — Fri, 23 Jun 2023 14:41:03 GMT

@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.

Re: Filename statement in a Macro

wbsjd — Fri, 23 Jun 2023 18:13:17 GMT

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

Re: Filename statement in a Macro

wbsjd — Fri, 23 Jun 2023 18:27:36 GMT

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.

Re: Filename statement in a Macro

wbsjd — Fri, 23 Jun 2023 18:34:15 GMT

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;

Re: Filename statement in a Macro

Tom — Fri, 23 Jun 2023 19:14:32 GMT

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 ?

Re: Filename statement in a Macro

wbsjd — Fri, 23 Jun 2023 20:48:37 GMT

%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

Re: Filename statement in a Macro

Tom — Fri, 23 Jun 2023 20:54:15 GMT

Then why did you call it from the data step with the extra .xlsx added?

From the photograph you posted of your SAS log your data step generated this command:

%zip(file_name=%nrstr(test.xlsx));

Instead of the the command you wanted:

%zip(file_name=%nrstr(test));

Fix one of the

The input dataset with the list of file names.
The macro to not add the .xlsx.
The data step to remove the .xlsx.

Re: Filename statement in a Macro

wbsjd — Fri, 23 Jun 2023 20:58:32 GMT

You are right! It is because the extra .xlsx in the Macro....... After I remove the xlsx in the Macro, the code works as assumed. I should check it carefully 😥

Re: Filename statement in a Macro

ballardw — Fri, 23 Jun 2023 22:09:33 GMT

@wbsjd wrote:

The SAS Log show no error and is like this:

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

I highlighted an error in your understanding above. Your output clearly shows RC=20006.

From the documentation of the FCOPY function, which is what your Put RC= would be printing from your %ZIP macro,

Values That Are Returned by the FCOPY Function

FCOPY returns these values:

a value of 0 if records were copied without errors or warnings

a positive value if an error occurred

a negative value if a warning was issued

The magenta is where you did not understand that your code was throwing an error.

Follow up with:

You can use the SYSMSG function to retrieve error or warning messages, and you can use the SYSRC function to retrieve the return code.

So you might use something like

if rc ne 0 then do;
   message=sysmsg();
   put message;
end;