Connection to server "Local" has been lost

[nelsonc]

I have a sas program that collects all the data i need but when i try and export it to excel i loose my connection to "local".

this code exports 4 seperate files and will fail after 2 export no matter which two i export. This code has worked before but is not working now. Could this be a firewall issue? Need some help. here is the export code that causes the disconnect. All tables exported are less then 2000 lines.If I run in EG it tells me i lost connection, if i run is base it just closes out after exporting two files and gives no errors.

/*-------------------------------------------------------*/
/*-----------------Export To Excel File------------------*/
/*-------------------------------------------------------*/
PROC EXPORT DATA=BIS_Campaign
OUTFILE= "\\cifs1\blahblahblah\BIS_Campaign.xlsx"
DBMS=EXCEL REPLACE;
SHEET="Sheet1";
NEWFILE=Yes;
RUN;

PROC EXPORT DATA=BIS_Sub
OUTFILE= "\\cifs1\blahblahblah\BIS_Subs.xlsx"
DBMS=EXCEL REPLACE;
SHEET="Sheet1";
NEWFILE=Yes;
RUN;

PROC EXPORT DATA=BIS_Wait_Final
OUTFILE= "\\cifs1\blahblahblah\BIS_SubDetail.xlsx"
DBMS=EXCEL REPLACE;
SHEET="Sheet1";
NEWFILE=Yes;
RUN;

PROC EXPORT DATA=BIS_Sent_Final
OUTFILE= "\\cifs1\blahblahblah\BIS_Sent.xlsx"
DBMS=EXCEL REPLACE;
SHEET="Sheet1";
NEWFILE=Yes;
RUN;

[Tom · Accepted solution]

Did you try using DBMS=XLSX so that you are not dependent on any Microsoft products working properly?

[nelsonc]

I just tried switching it to XLSX and this does work.

However this creates a .bak file for each, is there a way to stop this form creating .bak files without writing a macro to delete the .bak files?

[Tom]

You don't need a macro to delete a file, but you could make one if you want.

You can either remove any existing file before trying to re-create it.

filename outfile "\\cifs1\blahblahblah\BIS_Campaign.xlsx";
%let RC=%sysfunc(fdelete(outfile));
PROC EXPORT DATA=BIS_Campaign OUTFILE=outfile DBMS=XLSX REPLACE;
  SHEET="Sheet1";
  NEWFILE=Yes;
RUN;

Or remove the .bak file afterwards.

[NextVersion]

DBMS=XLSX works if you do this programmatically. However if you use the File Import/Export from the SAS Menu the issue remains of SAS Crashing. Is there also a way to change the default behavior that the SAS menu chooses in order to do an Import/Export?

[ballardw]

---

@nelsonc wrote:

I have a sas program that collects all the data i need but when i try and export it to excel i loose my connection to "local".

this code exports 4 seperate files and will fail after 2 export no matter which two i export. This code has worked before but is not working now. Could this be a firewall issue? Need some help. here is the export code that causes the disconnect. All tables exported are less then 2000 lines.If I run in EG it tells me i lost connection, if i run is base it just closes out after exporting two files and gives no errors.

---

Highlighted text tells me it is likely that something changed. One would suspect on your server side. Talk to you SAS Admin about software or settings that may have changed on the server.

[Sajid01]

Hello @nelsonc 
Is SAS installed on your local machine and the output destination mapped to your local machine ?
if the answer is yes, then try restarting your machine.

[PeteLund]

I'm having a very similar issue, but using PROC IMPORT with EXCEL rather than EXPORT.  The following code is the result of a macro call, but I have the same issue whether the code is run from the macro call or just run itself:

PROC IMPORT OUT= WORK.ComorbidityLabels DATAFILE= "\\coconuts3\data\AlaskaTrauma\ProjectFiles\Control\AnnualReportLabels.xlsx" DBMS=EXCEL REPLACE;
RANGE="ComorbidityLabels$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

If I run this in EG, where the macro that creates the IMPORT code is generated, I randomly get a "Error: The connection to the server has been lost."  This code has run just fine for many months and just yesterday started acting this way.  The code runs fine in DM.  SAS and all the data are on the same machine.  Using 9.4M7 and EG 8.3.0.103.

[Tom]

Enterprise Guide connects to a SAS server. That message sounds like Enterprise Guide has lost the connection to the SAS server.

Have the administrator of the server that the SAS session is running on try to figure out what is happening.  Perhaps the I/O between the server and network disk you are reading from is causing the connection between your PC and the SAS server to not respond quickly enough.  So Enterprise Guide thinks the server is gone.

You should probably raise a support ticket with SAS support.

[PeteLund]

As I mentioned in my post, everything, SAS and data, is local (on the same machine).  I'm the "SAS administrator" as it's a PC SAS  license on one workstation.  The job literally takes a fraction of a second to run, returning about 20 rows from the spreadsheet - hardly time for the connection to get hung up.  The main thing though is that this job has run thousands of times and just started doing this yesterday - no changes to SAS or EG installs.  I'm think it's a Microsoft update issue, but am having trouble seeing how this would affect EG differently than DM.

[PeteLund]

I'm amending my earlier post - this does fail in DM as well.  I created a simple two-cell spreadsheet with a "header" row of "Quote" and a data "row" of "Hello World!" in c:\temp\HelloWorld.xlsx and am running PC SAS on the same machine.  In either EG or DM the following code runs just fine once or twice and then fails.  In EG, I get the "Lost connection to server" message and in DM, the session just goes away.  I get the exact same behavior on two different machines.

PROC IMPORT OUT= WORK.ComorbidityLabels DATAFILE= "c:\temp\HelloWorld.xlsx" DBMS=EXCEL REPLACE;
RANGE="Sheet1$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

And a new wrinkle, it's not just PROC IMPORT.  I get similar results using the Excel LIBNAME engine.  This code runs fine two or three times and then fails:

libname test excel "c:\temp\HelloWorld.xlsx";

data test;
  set test.'Sheet1$'n;
run;

libname test clear;

In EG, sometimes I get the server connection lost message and sometimes a different message, "Error: Integration technologies failed to provide server interface. The remote procedure call failed. (Exception from HRESULT: 0x800706BE)"

I'd be interested if anyone can run either of the above a number of times in a row with success on a similar setup: PC SAS 9.4M7 on the local machine, Windows 10 Pro, Office 365.  Again, I have the same issues on two different machines and the real code that exposed the issue has been running unchanged for months and this just popped up this week.

[Tom]

In this case the root cause is probably the DBMS=EXCEL.  The error message is probably talking about some "connection" that the EXCEL engine is making to the Microsoft Excel product that SAS is calling to read/write the XLSX file.

Try changing that using DBMS=XLSX and see if the error disappears.

[PeteLund]

XLSX on PROC IMPORT is not really an option for me in the real world as, at least last I checked, it does not support reading from a specific worksheet.  It will always read from the first one and many of the spreadsheet I'm pulling data from have multiple worksheets and I need to reference particular ones.

[Tom]

---

@PeteLund wrote:

XLSX on PROC IMPORT is not really an option for me in the real world as, at least last I checked, it does not support reading from a specific worksheet.  It will always read from the first one and many of the spreadsheet I'm pulling data from have multiple worksheets and I need to reference particular ones.

---

That is not true.  You can tell PROC IMPORT which sheet to read from an XLSX file.

[PeteLund]

Thanks Tom - you are correct.  I've always used RANGE with DBMX=Excel and you get away with just referencing a worksheet name (with no range specified) and IMPORT will read the entire sheet.  With DBMS=XLSX the RANGE= statement needs to specify a range, but the SHEET= statement works fine with XLSX to read the whole sheet.

While this does work, I've tracked down about 150 places in my code I've used DBMS=Excel that might have issues.  Fortunately, the IMPORT always works when just called once, so often not a real issue.