BookmarkSubscribeRSS Feed
Walternate
Obsidian | Level 7

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. 

4 REPLIES 4
ballardw
Super User

How many variables? How many observations?

 

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

Walternate
Obsidian | Level 7

Only about 1000 observations and 120 variables. 

ChrisHemedinger
Community Manager

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

 

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Kurt_Bremser
Super User

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)

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