04-13-2016 04:06 PM
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
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.
04-13-2016 04:14 PM
How many variables? How many observations?
If you want variable Labels to appear as column headers you will want to specify the option Label.
04-13-2016 04:20 PM
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).
04-14-2016 03:50 AM
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)