BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
wbsjd
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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 

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

View solution in original post

17 REPLIES 17
SASKiwi
PROC Star

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
Obsidian | Level 7
Hello,

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

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

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

call execute('%nrstr(%zip(file_name='||strip(file_name)||');)');
wbsjd
Obsidian | Level 7
It still doesn't work...
ballardw
Super User

@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
Obsidian | Level 7

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 

wbsjd_1-1687543761346.png

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 

wbsjd_2-1687543926140.png

 

I really don't know what's the issue

 

 

Tom
Super User Tom
Super User

@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
Obsidian | Level 7
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
PROC Star

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
Obsidian | Level 7

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
Super User Tom
Super User

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
Obsidian | Level 7

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

wbsjd_1-1687544546386.png

 

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:

wbsjd_2-1687544761104.png

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

Tom
Super User Tom
Super User

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
Obsidian | Level 7

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 3137 views
  • 4 likes
  • 4 in conversation