Help using Base SAS procedures

losing format when export to xlsx

Reply
Super Contributor
Posts: 259

losing format when export to xlsx

Hi

I have a dataset in sas with all the fields formatted the way I want but when I export to xlsx using the code below, I lose the formats.

proc export

data = dataset1


outfile = "...\dataset.xlsx"

dbms = xlsx

replace;

sheet = final;
run;

Any way to keep the format?

Super User
Posts: 6,938

Re: losing format when export to xlsx

Try ODS TAGSETS.EXCELXP to create formatted data. When using proc export, Excel may do its own formatting.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,401

Re: losing format when export to xlsx

As KurtBremser notes, Excelxp tagset will give you more flexibility in your output files.  Here is an article talking about proc report + tagset + mso formats:

https://communities.sas.com/thread/47133

Super Contributor
Posts: 259

Re: losing format when export to xlsx

I want to export to xlsx. I've tried using ODS TAGSETS.EXCELXP as...

ODS tagsets.ExcelXP FILE="...\oneway_output_by_year.xlsx";


PRINT DATA=oneway_all4;

run;

ods tagsets.ExcelXP CLOSE;

When I run this I get an message saying "Do you want to open or save this file". When I select opn, I get a message saying "excel cannot open the file oneway_output_by_year.xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted or and that the file extension matches the format of the file.".

Do you know what I'm doing wrong?

Can the export be automated or do you have to save each time manually? 

Super User
Super User
Posts: 7,401

Re: losing format when export to xlsx

Well, I would change the proc print to be a proc report first off.  Also add a style.  Change the file extension to XLS:

ods tagsets.excelxp file="xyz.xls" style=statistical;

proc report data=oneway_all4 nowd;

     columns _all_;

     define ...;

run;

ods tagsets.excelxp close;

Note that the tagset actually creates XML output, which Excel then reads in and interprets.  To also note that in SAS 9.4 there is a libname statement through which you can create native XLSX files: SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition

Before 9.4 you would need to know the Open Office format to create them.

Super Contributor
Posts: 259

Re: losing format when export to xlsx

Thank RW9 but I'm still getting that error message when I try to open the file -when I do open it the format it the way I want it but the error message is annoying.

Super User
Super User
Posts: 7,401

Re: losing format when export to xlsx

Yes, the "warning" message you get about the file being in a different format than expected is actually an Excel one, not controllable by SAS.  What it is saying is that a file with the extension XLSX is expected by Excel to be in its Open Office format.  What you are creating is XML.  So in reality you would want to create the file as xyz.xml, however on most peoples machines XML will open with a web browser not Excel by default, so by using the .xlsx extension you are fooling Windows into thinking it should open the file with Excel.  You could of course ask everyone to associate XML files to open with Excel and then you would avoid this message, but its probably a lot of effort.  If you have 9.4 then try the libname variety.

Super User
Posts: 6,938

Re: losing format when export to xlsx

Save the file as .xml, and you don't get the warning.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 7 replies
  • 892 views
  • 0 likes
  • 3 in conversation