Note: as this is a popular topic, I've added a few notes with minor updates, including a link to a popular how-to tutorial video.
In case you missed it, the first maintenance release for SAS 9.3 was recently released. Because we're all friends here, you may call it "SAS 9.3M1" for short.
Maintenance releases are usually about fixing problems that SAS has found or that customers have reported. However, sometimes a new capability might sneak out the door along with it. That's what happened this time with improved XLSX support in PROC EXPORT.
Now for the first time, this round-trip import/export cycle works just as you would expect with no additional setup required:
proc export data=sashelp.prdsale dbms=xlsx outfile="c:\temp\prdsale.xlsx" replace; run; proc import datafile="c:\temp\prdsale.xlsx" dbms=xlsx out=work.prdsale replace; run; |
Watch this tutorial (from SAS instructor Kathy Kiraly) to learn more about how to import and export XLSX files in SAS with just a few lines of code.
Kathy's tutorial includes simple shortcuts, such as using the LIBNAME XLSX engine to read Excel files just like data sets.
Remember: using the IMPORT and EXPORT procedures to read and write Microsoft Excel files requires the SAS/ACCESS to PC Files module. With these latest changes, you can get the job done without setting up a PC Files Server, even on systems that don't have a 32-bit Windows architecture.
Handpicked Related Content: How do I export Excel files, let me count the waysPrior to this support, most customers who run SAS for 64-bit Windows or on a UNIX platform would need to use DBMS=EXCELCS for this operation. The EXCELCS method works by delegating the Excel read/write operation to another Windows node that has a PC Files Server instance. (On 64-bit Windows, you can make it work with very little configuration using the "autostart" capability.)
The PC Files Server is still very useful for other scenarios, such as supporting the PCFILES LIBNAME engine, which can read and write Microsoft Excel and Microsoft Access files, among others.
And if you're looking for a point-and-click method for creating XLSX files in SAS Enterprise Guide, we cracked that nut a while ago. Just look for the Export as a Step task on your data view, or the "Share" tool in SAS Enterprise Guide 8.1.
WANT MORE GREAT INSIGHTS MONTHLY? | SUBSCRIBE TO THE SAS TECH REPORT
69 Comments
Thanks! I have been struggling with this issue!
Pingback: How do I export SAS to Excel files: Let me count the ways - The SAS Dummy
So, does this latest release fix the known issue with corruption of named ranges in xlsx/xlsm files under 9.2? From the track I had running a while ago, this was due to an issue with the MS Jet Engine which I am guessing has been fixed in 9.3M1.
Richard,
I cannot say for certain whether this fixes the issue you saw. It's true that this update allows you to create an XLSX file without invoking the Microsoft drivers (which seemed to be to blame for the issue you reported). But I'm not sure that you can use this method to accomplish your objective...especially if you are wanting to create a macro-enabled file (XLSM). That question might be work tracking again with SAS Technical Support.
Thank you Chris. I don't have 9.3M1 yet - will need to wait until it is deployed at our site. In the meantime, I will stay with xls files for proc export, or a modified version of exportToXL (DDE maco suite) for xlsx or xlsm. Unfortunately exportToXL doesn't work with hidden worksheets.
I'm facing some problem in creating EXCEL file under LINUX thru proc export.
Can you please suggest me the best way to create or export data to excel on LINUX platform itself?
Ganesh,
If you have SAS 9.3 Maint 1, then you can use the XLSX method described in this post. For a more complete survey of all of the possibilities, check out "How do I export Excel files, let me count the ways".
Hi Chris,
I want to write xlsx file in unix server using unix sas code. My Unix Sas aricheture is ts1m1 64 bit and I am having 32 bit excel microsoft 2007. When I am using below code for exporting the xlsx file in unix server location.
Code used :
proc export data=
outfile=
dbms=xlsx
replace;
run;
xlsx file getting created in the unix location and after ftping to the local machine using ftp application. I am getting this error.
'Excel found unreadable contents in 'filename.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click yes
Could you please help me on this
When you FTP the file, are you certain that you are using BINARY mode as the transport? If not, the file could become corrupted in the transfer.
XLSX files are ZIP-formatted archives, so you could try to open in a tool such as 7Zip or WinZip to make sure the file is at least a valid archive.
Chris... What would my proc export statement look like if I have SAS V9.1.3 and want to export to Excel 2010 because that's what I have on my PC?
Marc,
In SAS 9.1.3 there is not native support for writing to XLSX files. You can still use DBMS=EXCEL of course, but you will still have the limitations of the XLS format: 65K rows, etc.
As an alternative, you can use DBMS=CSV, ODS TAGSETS.EXCELXP, or ODS CSV output. I have a more complete list of methods available here.
Pingback: The top gotchas when moving to 64-bit SAS for Windows - The SAS Dummy
Works great; fixes a workflow bind.
But how does one specify a particular sheet (if it's not the first worksheet in the workbook)
Check out many options for Excel export flexibility in this post.
Subci, you currently can't specify a particular sheet to export to or to import from by using SAS Interface to PC Files. You'd need to download and install SAS 9.3 PC Files Server.
All you need to know is explained here: http://support.sas.com/kb/43/802.html
Good luck
I have found the following allows me to create worksheets within the same workbook (using the code example in the original post):
proc export
data=sashelp.prdsale
dbms=xlsx
outfile="c:\temp\prdsale.xlsx" ;
sheet = sheet1 ;
run;
proc export
data=sashelp.prdsale
dbms=xlsx
outfile="c:\temp\prdsale.xlsx" ;
sheet = sheet2 ;
run;
I hope this helps someone, as it was a lifesave for my team! Thanks!
Jessica, thanks for sharing. I believe that the ability to add sheets to an existing workbook is a new feature in SAS 9.4.
Works great!
Is there a list of options for this output type? I can't find any documentation on it. The defaults works great, but I'd love to write to a specific worksheet, add autofilters, freeze the first column and/or row, etc. Thanks!
I think what you want is the features of the TAGSETS.TABLEEDITOR, which I didn't mention in this post. That has the ability to create more control over the output columns like you're asking for.
Sorry, Doesn't work...
38 %LET filrf = "/sas/sasdata/sashome/41000769/bilstod/2012/&prognos/Utbet_exc.xlsx";
170
171 proc export data=bet5_3
172 outfile=&filrf
173 dbms=xlsx replace;
ERROR: DBMS type XLSX not valid for export.
--- SAS-programvara ---
SAS Enterprise Guide-version: 4.3.0.10196
SAS System-version: none
SAS System-kommando: none
Shared SAS-files: D:\Apps\SAS\SharedFiles
JMP-installation:
--- Actual process ---
Actual catalog: d:\apps\sas\enterpriseguide\4.3
Commandorow: d:\apps\sas\enterpriseguide\4.3\SEGuide.exe
Space for tempfiles: C:\TEMP\7\SEG55896
Memory used: 202 633 216 bytes
Type of clientsession: Terminal services client
Runs in WOW64 (32-bit subsystem): Nej
--- Operativsystem ---
Namn: Windows 2003
Version: 5.2.131072.3790
Servicepack: Service Pack 2
.NET Runtime Version: 2.0.50727.3634 (SP 2)
Internet Explorer-version: 6.0.3790.3959
Systemkatalog: C:\WINNT\system32
Standardwebreader: Citrix Metaframe Server FTA
MDAC-version: 2.82.3959.0
In order for this to work, you need SAS 9.3 maint 1 (at least) and SAS/ACCESS to PC Files on the SAS server. It's not clear from your log whether you have that; I suggest redirecting follow up questions to SAS Technical Support.
Pingback: The best of SAS blogs for 2012 - SAS Voices
Is there any way to pass SAS formats to Excel when using PROC EXPORT DBMS=XLSX? I'm exporting a dataset with some PERCENT-formatted columns, but they just show up as raw numbers in Excel.
If you want to stick with DBMS=XLSX, you might try creating a character-formatted column with put(col,percent5.) - for example - and see if that carries over.
Otherwise, you can use ODS tagsets.EXCELXP or LIBNAME EXCEL (or LIBNAME PCFILES) to put your data to a spreadsheet and perhaps retain the formatting you want. See this blog post for an inventory of various methods.
Chris,
Thanks so much for this helpful tip! I notice that dbms=xlsx seems to apply some sort of compression to the xlsx file, since when I open it up and immediately resave with a different name, the new file is larger. Do you know anything about this?
Andrew, I don't know what would make the file larger, exactly...but here's what I do know. The XLSX file format is a ZIP archive, compressed already. SAS writes to this file directly, so it creates a no-frills version of your SAS content for use in Excel. When you open and resave the file in Excel, perhaps Excel "enriches" the file with additional information, such as formatting instructions, so the resulting file is a bit larger.
That's my made-up answer. It might be at least partially correct.
Thanks. I guess my particular problem is that when I try to pass SAS-created xlsx files as attachments through outlook to colleagues, they get quarantined for "bad compression", but if I open them, resave, and then attach to an email, they go through fine...could be something specific to my work environment.
Andrew, it could be that the XLSX file doesn't "smell" like a true XLSX file to your e-mail filters, since it wasn't created with the Microsoft tools. It might be interesting to examine the file versions, before and after the re-save operation. You could work with SAS Tech Support on this and if a change needs to be made to DBMS=XLSX processing, they can help get that in the system.
Chris, thanks for your help. I was not able to comprehensively follow up on the issue, but I believe that it was a problem with my work environment unrelated to SAS.
Hi Chris,
The above syntax is not working for Mainframes SAS, is there any specific solution to export the data in excel 2007 file (.xlsx) in SAS mainframes...
Thanks and Regards
Babu - I see that z/OS is not listed in the support matrix for PROC EXPORT and XLSX. You can still use ODS TAGSETS.EXCELXP, or straight-line CSV. If you need another option, please contact SAS Technical Support for some ideas.
Chris,
We just installed 64-bit SAS 9.4 (TS1M0) under Windows 7. Using PROC IMPORT with DBMS=XLSX works, except that the RANGE statement seems to be ignored and always the first worksheet in the Excel file being imported. This makes it pretty useless when I want to import multiple worksheets from a single Excel file. So, I am sticking with using DBMS=EXCELCS with the 32-bit PC Files Server installed, which seems to work fine with a RANGE statement. Any chance of a fix so that the RANGE statement will work anytime soon?
Mark,
The XLSX method received some updates in SAS 9.4 TS1M1. I believe the RANGE= works better, plus there is support updating/adding a sheet to an existing workbook.
I suggest confirming this for your scenario with SAS Technical Support, and then pursue the M1 update if you can.
With SAS 9.2 this simply does the trick as well
Wouter,
Interesting find! I think in this case, it's the XLSX extension that is triggering the file format. If you specify the same code (EXCEL2000) and an XLS file extension, you'll see the file format is different.
Pingback: Using ODS EXCEL and PROC EXPORT to bundle Excel-based reports - The SAS Dummy
Thank you so much Chris your tip works perfectly
Pingback: Performing Logistic Regression in R and SAS | Mubashir Qasim
I get the following error message when trying to import data from excel. I am using SAS 9.4 and my desktop is running on windows 7 with 64 bit OS.
Proc import out=MHET112_A1
192 datafile= "C:\Users\.....\Documents\MHET112_A.xlxs"
193 dbms=xlxs
194 replace;
ERROR: DBMS type XLXS not valid for import.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
195 getnames=yes;
196 run;
Are you sure that the file extension isn't XLSX? The proper file extension for most Microsoft Excel files has the "S" before the final "X".
Hello, after the file import with XLSX I have very strange columns behaviour - length of the actual data is equal to the length of the column, for example I try to create new integer column from string and get the following note:
NOTE: Invalid numeric data, '22247...' and new column is empty after that
I concatenated '!!!' to all values and got the following result - '22247 !!!' - can't compress it either. Do you ever saw such a thing?
Loc,
How are you importing this XLSX file? Using SAS Enterprise Guide and the Import Data task? Or are you using PROC IMPORT? If you can supply more details, I suggest that you post the question to communities.sas.com. The experts there can probably answer your question quickly.
It was PROC IMPORT. Will try to past it there, thanks
The code works good but,,, when I open the excel file a msg pops up that the file is corrupted and I couldn't open it!!
If you want to follow up on this error, post in communities.sas.com with the details of the code you're running, the version of SAS that you have installed, and the error message. Or, open a track with SAS Technical Support.
How do I change the column width? The width of each column looks either too narrow or too wide in Excel. How do I change it in the Proc Export statement? Thanks!
Dan,
PROC EXPORT simply exports the data, and doesn't tell Excel how to format the appearance at all.
There are two other mechanisms that provide a little bit more control. First, you could try ODS EXCEL, which has several appearance-related options. ODS EXCEL is new in SAS 9.4. Or, you could try the new LIBNAME XLSX engine -- it doesn't have formatting options but since it creates a native XLSX file, the output format might look a little bit better.
Thank you very much! Here is my code. The problem was the text in a couple of columns got truncated in the Excel 2010. But these columns look very nice in SAS 9.4 dataset. The columns weren't truncated in SAS; but it's trruncated after ods to excel. Please help!
ods excel file="c:\test.xlsx"
style=Pearl
options(sheet_interval="proc" sheet_label="none" sheet_name="Test");
proc print data=test noobs; run;
ods excel close;
Dan,
You can use additional options to influence the column widths. For example:
ods excel file="c:\test.xlsx"
style=Pearl
options
(sheet_interval="proc"
sheet_label="none" sheet_name="Test"
absolute_column_width='20');
I don't think there is an "autofit" option.
I used your code and it works, but I have first few lines of comments in the excel before the data.
Is there a way to tell MIXED data in the file. That way it will only copy the data into dataset instead of comments as variable.
proc import
datafile="c:\temp\prdsale.xlsx"
dbms=xlsx
out=work.prdsale
replace;
run;
Try the DATAROW= statement in PROC IMPORT, which tells the procedure where to begin reading.
Pingback: SAS and Excel 2010. XLSX. | SAS-Education
Hi. I'm using this to export, and it works well except that if I export more than 1 spreadsheet to a workbook, the spreadsheets are all linked. If I don't remember to unlink them, any changes in one affects all the others. Is there some way to stop this? (It happens both in a plain proc export and a ODS export to xlsx; it never happened when I was using xls.)
I wasn't aware the sheets become linked. Can you share specifics about how you are creating these XLSX files? Perhaps post the details in SAS Support Communities -- your fellow users will test and advise!
Hi All,
Kindly do me a favor.
How to IMPORT .xlsx file into SAS UNIX environment.
I tried
PROC IMPORT DATAFILE="path/abc.xlsx" OUT=ABC DBMS=XLSX REPLACE;
RUN;
But I am getting ERROR: DBMS type XLSX not valid for import.
NOTE: In DBMS I also used DBMS=EXCEL or DBMS=EXCEL2000 but getting same error.
Kindly give your valuable inputs.
Thank you.
Regards,
Kunal
First, you need SAS/ACCESS to PC Files -- can you verify you have that with proc product_status?
Next, DBMS=XLSX works with SAS 9.4 reliably, but not earlier. For that, you might need to use DBMS=XLS. But for XLSX files, you probably would need to set up a PC Files Server (again, this is only for releases earlier than SAS 9.4).
Enormous thanks, this code worked wonders for me. Thanks once again
But it doesn't work for multiple sheet by macro. The new sheet will replace the old one. Is any solution for macro output to xlsx?
%macro ex(oa,oaname,prod);
PROC EXPORT DATA=pool_&oa
OUTFILE="&data_oa.\List for &oaname._20&today..xlsx"
DBMS=XLSX REPLACE;
sheet="&prod.1st";
RUN;
%mend ex;
%ex(P,AA,CC);
%ex(P,AA,PL);
You don't mention the SAS version you're running. Replacing content at the sheet level is supported in SAS 9.4 -- is that what you're running?
Can someone help getting the following error wile exporting class dataset to local machine.
Insufficient authorization to access
/opt/sas/config/Lev1/SASApp_FIN_OPSPAY/\\GBMLVFILFS04N02\Home4$\sahuaad\Profile\Desktop\class.xls.
Your SAS is running on UNIX (maybe Linux, and maybe SAS University Edition?). If running in SAS Enterprise Guide, you probably need to use this technique. If using SAS University Edition, you need to target the shared folder in your VM (path = '/folders/myfolders'). See this FAQ.
Can Someone Help Getting the following error while exporting report_daily dataset to local machine.
ERROR: Temporary file for XLSX file can not be created ->
/opt/sasinside/SASConfig/Lev1/SASApp/D:\SASUniversityEdition\myfolders\Daily\/MTD_AMDOCS.$$1. Make sure the path name is correct
and that you have write permission.
Using SAS University Edition, you must specify the proper path for your shared folder in the PROC EXPORT file out option, i.e., "/folders/myfolders/Daily\/MTD_AMDOCS.xlsx".
Pingback: How do I export from SAS to Excel files: Let me count the ways - The SAS Dummy
I currently use SAS in a Linux environment. I have a 10k obs dataset that will be exported to an Excel spreadsheet (.xlsx file) using proc export.
Everytime I export, I need to manually format the output (column widths, column headers shading, etc.). Would there be a way to export to an excel file using a pre-existing excel template?
That would save me lots of time. Please, advise. Thanks!
You can automate those styles/widths by using ODS EXCEL and its many options. Here's a good paper on the topic.
If you wanted to use a predefined template and you have SAS Add-In for Microsoft Office, you could design your workbook and then use SAS to pull the content into place.
proc sql;
create table WORK.Validations2 as
select distinct Lease_Type_Desc
from WORK.Validations1
group Lease_Type_Desc;
run;
proc export
data=VALIDATIONS2
dbms=EXCEL2000
outfile="C:\temp\prdsale.xlsx"
replace;
run;
I got the output Data but file is not being created.
Why use EXCEL2000? Best advice is to use DBMS=XLSX these days for native Excel files.