Desktop productivity for business analysts and programmers

Error exporting transposed data to XLSX

Reply
Occasional Contributor
Posts: 12

Error exporting transposed data to XLSX

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?

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Error exporting transposed data to XLSX

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.  

Occasional Contributor
Posts: 12

Re: Error exporting transposed data to XLSX

[ Edited ]

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.

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Error exporting transposed data to XLSX

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.  

Grand Advisor
Posts: 10,210

Re: Error exporting transposed data to XLSX

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.

Occasional Contributor
Posts: 12

Re: Error exporting transposed data to XLSX

The transposed data sets have less than 10 rows

Grand Advisor
Posts: 10,210

Re: Error exporting transposed data to XLSX

Number of variables (columns)?

Occasional Contributor
Posts: 12

Re: Error exporting transposed data to XLSX

2 columns, and I was wrong before. There are 14 rows, not less than 10.

So, 2 columns and 14 rows.
Super User
Super User
Posts: 6,326

Re: Error exporting transposed data to XLSX

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.

Occasional Contributor
Posts: 12

Re: Error exporting transposed data to XLSX

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.
Super User
Super User
Posts: 6,326

Re: Error exporting transposed data to XLSX

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

Ask a Question
Discussion stats
  • 10 replies
  • 538 views
  • 0 likes
  • 4 in conversation