topic Re: proc export seems to be having issues with character vars in SAS Enterprise Guide

proc export seems to be having issues with character vars

Walternate — Wed, 13 Apr 2016 20:06:45 GMT

Hi,

I have a SAS dataset which I am trying to convert to an xls file using SE Guide 4.3 and the following code:

proc export data=data1
dbms=xls
outfile="filepath\excel_file.xls"
replace;
run;

According to the log, the code runs fine with no issues whatsoever. However, when I open the Excel file, I get an error message that the file was corrupted and needs to be repaired by Excel so it can be readable. When I repair it, there are no variable names/headers in the file, and it looks like only the numeric vars are filled in--the character variables seem to be missing altogether. I do have a number of long-ish character variables, but not extremely so--the longest is 100 characters.

Any help is much appreciated.

Re: proc export seems to be having issues with character vars

ballardw — Wed, 13 Apr 2016 20:14:07 GMT

How many variables? How many observations?

If you want variable Labels to appear as column headers you will want to specify the option Label.

Re: proc export seems to be having issues with character vars

Walternate — Wed, 13 Apr 2016 20:15:37 GMT

Only about 1000 observations and 120 variables.

Re: proc export seems to be having issues with character vars

ChrisHemedinger — Wed, 13 Apr 2016 20:20:05 GMT

You don't say what version of SAS, but if 9.3 or later you could try DBMS=XLSX. DBMS=XLS uses a back-level file format for Excel -- even older than what most people think of when looking at "XLS" files.

Another alternative if on Windows: DBMS=EXCEL or DBMS=EXCELCS (64-bit SAS with 32-bit MS Office).

Re: proc export seems to be having issues with character vars

Kurt_Bremser — Thu, 14 Apr 2016 07:50:03 GMT

Write a .csv file with a data _null_ step from SAS, then open that with Excel, to see if it's the file format or a limitation of your Excel version.

When that is verified (Excel can deal with all the data), do a simple proc print within ODS TAGSETS.EXCELXP (use a filename with a .xml extension) and open that with Excel. The xml file will have considerable size, but usually works quite well (and can be checked with a text editor)