BookmarkSubscribeRSS Feed
JoshS
Fluorite | Level 6

I am doing something I do all the time, exporting data sets out to an excel sheet. However, this time I am getting an error that doesn't seem to make sense for what I am doing. The error is as follows:

 

An error occurred executing the workspace job "programNameHere". The server is disconnected

I've narrowed it down to only getting the error when I try to export specific data sets. The common denominator amongst these data sets is that they are generated from a PROC TRANSPOSE. The other data sets being exported don't cause the error when I run the program with the transposed data sets removed.

 

It is interesting to note that the log stops recording lines above where the export is taking place AND the rest of the code still seems to execute AND (here's the kicker) the excel sheet is generated and populated correctly. While this still seems to "work," it's messy and I don't want to rely on this shady method for production purposes.

 

CODE:

proc transpose data=toBeTransposed
               out=transposedData;
run;

PROC EXPORT
	DATA =transposedData
	OUTFILE = "filenamehere.xlsx"
	DBMS = xlsx 
	REPLACE;
	SHEET = "mySheetName";
RUN;

Any thoughts?

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, its not really an answer to your question, but me personally I never use proc export.  Why not use excelxp tagset and proc report, far more control, and formatting options.  If you need a "real" xlsx file, then libname excel?  If you need a plain file with just the data, then CSV would be the option.  For strcutured data then use XML.  

JoshS
Fluorite | Level 6

I'm not familiar with those methods you've mentioned. Do you think you could point me in the direction of some tutorial? Or even a snippet of clear cut code that would do the basic function of dumping data to an excel sheet? (I'll do a bit of googling as well!) 

 

I could do the csv, but I don't think that supports multiple tabs as it isn't really an excel file.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sure:

Tagsets.excelp:

http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

 

libname Excel:

http://www2.sas.com/proceedings/sugi31/024-31.pdf

 

With regards to the CSV, it depends on what you are doing.  If the information is for review, then I would suggest PDF/RTF as a preferred medium.  If the data is for transfer, then CSV or XML would be preferred formats.  

ballardw
Super User

How big are the transposed data sets? Have you possibly exceeded the number or columns or rows? I believe there may be a limit , maybe 255, on the number of variables the export engine handles for XLSX in some implementations.

JoshS
Fluorite | Level 6

The transposed data sets have less than 10 rows

ballardw
Super User

Number of variables (columns)?

JoshS
Fluorite | Level 6
2 columns, and I was wrong before. There are 14 rows, not less than 10.

So, 2 columns and 14 rows.
Tom
Super User Tom
Super User

What are the column names?  I had trouble with XLSX in an earlier release when the column names looked too much like Excel's notation of cell references. 'A1' for example.

JoshS
Fluorite | Level 6
I've tried the default names after the transpose "_NAME_" and "COL1". I've tried renaming them to different things, none of which are close to cell references, and the same error persists.

Keep in mind, this is generating the actual excel sheet, and it looks good. But for some reasons it errors and crashes SAS.
Tom
Super User Tom
Super User

I would suggest creating a simple example that re-creates the problem and reporting it to SAS support.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 10 replies
  • 1869 views
  • 0 likes
  • 4 in conversation