Desktop productivity for business analysts and programmers

proc export seems to be having issues with character vars

Reply
Frequent Contributor
Posts: 138

proc export seems to be having issues with character vars

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. 

Super User
Posts: 11,114

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

How many variables? How many observations?

 

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

Frequent Contributor
Posts: 138

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

Only about 1000 observations and 120 variables. 

Community Manager
Posts: 2,887

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

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).

 

 

Super User
Posts: 7,405

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

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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 4 replies
  • 276 views
  • 0 likes
  • 4 in conversation