Using filename statement to move Excel sheets between servers - Enterprise Guide

[robulon]

Hi,

First of all I want to apologise in advance for my ignorance, I'm hoping to ask quite a generic question.

At work, I have used some code as a basis for something I have coded up myself that starts off by moving an Excel template sheet from a network drive into a folder in the Linux server that SAS is housed on using this code: -

filename windows "###########/Template";

filename netapp "##########\promise_analysis";

data _null_;

file netapp("PROM_SUMMARY.xlsx") recfm=n;

infile windows("PROM_SUMMARY_TEMPLATE.xlsx") recfm=n;

input c $CHAR1.;

put c $CHAR1. @@;

run;

Then, new data is created in SAS and exported to the Excel sheet like this: -

proc export data = prom_summary

outfile = ############/PROM_SUMMARY.xlsx'

dbms = xlsx replace;

sheet = PROM_SUMMARY;

run;1

And finally, the populated Excel sheet is moved back to the network drive like this.

filename windows "###########\Promise_Analysis";

filename netapp "###########\promise_analysis";

data _null_;

file windows("PROM_SUMMARY.xlsx") recfm=n;

infile netapp("PROM_SUMMARY.xlsx") recfm=n;

input c $CHAR1.;

put c $CHAR1. @@;

run;

My question is that when I run the process flow in full, the sheet that is move back to the network drive is the template without any updated data in it. If however, I run the last part of the process flow a second time, the populated sheet is copied over. The code I have used as a basis does the job correctly and I can't see anything in the log to suggest why this would be happening. Is there anything blatantly obvious that anyone can point out that I may be doing wrong?

Many thanks,

Rob

[robulon]

Sorry, I should also have added, the code is broken down into different programs as it uses different servers in the various stages. When the sheet is initially move to the SAS server, it uses Local (Connected), when it exports to the xlsx, it uses SASApp (Connected) and when it moves the updated sheet back to the network it uses Local (Connected) again.

Also, I did wonder if perhaps the code was running too fast for the updated sheet to be recognised so have tried putting a 15 second sleep in between the exporting to Excel and the moving of the table back to the network but this makes no difference. I have also tried putting the last step in a separate process flow but again, that seems to have no effect.

Thanks again,

Rob

[robulon · Accepted solution]

In case anyone is interested, or has the same issue, I've managed to get to the bottom of this.

As a new to EG user who has previously exclusively used Base SAS, I'd assumed that the programs in my process flow would run in the order they were displayed in the Project Tree, however this is not the case. By manually linking the programs in the order I want them to run, the issue has been resolved.

What was happening previously was that EG was running the code to move the file back to Windows before it had updated it.