topic Re: losing format when export to xlsx in SAS Procedures

losing format when export to xlsx

brophymj — Wed, 18 Feb 2015 09:46:45 GMT

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?

Re: losing format when export to xlsx

Kurt_Bremser — Wed, 18 Feb 2015 09:52:37 GMT

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

Re: losing format when export to xlsx

RW9 — Wed, 18 Feb 2015 10:09:13 GMT

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

Re: losing format when export to xlsx

brophymj — Wed, 18 Feb 2015 10:44:46 GMT

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?

Re: losing format when export to xlsx

RW9 — Wed, 18 Feb 2015 10:56:40 GMT

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.

Re: losing format when export to xlsx

brophymj — Wed, 18 Feb 2015 11:05:25 GMT

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.

Re: losing format when export to xlsx

RW9 — Wed, 18 Feb 2015 11:22:57 GMT

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.

Re: losing format when export to xlsx

Kurt_Bremser — Wed, 18 Feb 2015 11:34:10 GMT

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