02-18-2015 04:46 AM
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.
data = dataset1
outfile = "...\dataset.xlsx"
dbms = xlsx
sheet = final;
Any way to keep the format?
02-18-2015 04:52 AM
Try ODS TAGSETS.EXCELXP to create formatted data. When using proc export, Excel may do its own formatting.
02-18-2015 05:09 AM
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:
02-18-2015 05:44 AM
I want to export to xlsx. I've tried using ODS TAGSETS.EXCELXP as...
ODS tagsets.ExcelXP FILE="...\oneway_output_by_year.xlsx";
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?
02-18-2015 05:56 AM
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;
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.
02-18-2015 06:22 AM
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.
02-18-2015 06:34 AM
Save the file as .xml, and you don't get the warning.