If I want to generate an excel file, I want to use fomat funtion so that it can format a decimal correctly.
That is, if it originally generates a number like, 2.89888555, I want it be like, 2.90, or 2.9
outfile = "c:\tables"
dbms = XLSX replace;
can I use format to do that? Any other options?
------
For the second option then, round the data using the ROUND() function and then use Proc Export.
data class;
set sashelp.class;
weight=round(weight, 0.1);
height=round(height, 0.1);
run;
proc export data=class outfile='/folders/myfolders/sample.xlsx' dbms=xlsx replace; run;
can I use format to do that? - Yes.
Any other options? - No, not with PROC EXPORT.
In any case I would recommend to export the exact value to Excel and then use Excel functionality (possibly controlled by SAS) to display the value rounded to 1 or 2 decimals. This would avoid unnecessary rounding issues if people work with the Excel file you created, perform calculations, filter data (e.g. "all values <2.9"), etc.
I'm not sure if you can trigger the Excel formatting by PROC EXPORT or if you should better use ODS, as I don't have that SAS/ACCESS license. If it's not possible, but you still want to use PROC EXPORT and do the formatting (almost) automatically, you could record/write an Excel (VBA) macro to achieve this.
Would you please show me a sample excel macro code? Thanks
What version of SAS do you have?
How big is the dataset?
SAS 9.4
it has over 100000 obs
I didn't have such a macro on my computer, but I quickly created one by using Excel's user-friendly "Record Macro" function (please see Excel help for more [Excel version-specific] details, if necessary). After removing obviously unnecessary lines it looks as simple as this:
Sub twodec() Range("A1:A10").Select Selection.NumberFormat = "0.00" End Sub
As you can guess, it selects cell range A1:A10 and applies the number format with two decimals to these 10 cells. It's easy to apply a macro like this to any existing Excel sheet. (To learn how, you probably won't need the Excel help, because it's so intuitive.)
Just to mention SAS (as this is mandatory in this forum): In a recent project of mine I used SAS programs which created (meta-)data-driven VBA macros (for MS Word in that case) and executed them via an X command (calling winword.exe). So, it's possible to let SAS trigger the entire automation, but let the VBA engine do the actual formatting or whatever needs to be done with the MS Office files.
Thank you. This is a little hard for me to understand
Proc Export does not support writing of formats to Excel files.
Before going down the excel macro route you have a couple of other options:
1. Round actual data and present that, although the underlying data is nice, it's not always required.
2. Using Tagsets.ExcelXP to output an XML file which can be viewed in Excel. This file can then be saved as a native Excel file. There is also a VB script to convert said files to native Excel files automatically if you're interested.
http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html
3. Use ODS Excel if you have the latest SAS release
4. Use a macro written by some power users that generates the native excel file and allows you to choose if formatting is applied or not. This works well, but you have to understand how to call a SAS macro, which is a small learning curve though.
http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export
which one is the easiest one
I realize the macro not be the easy one to work on.
For the Tagsets.ExcelXP, I read a little bit but still do not get what I need. I only need the option to change the number from "2.377777" to "2.38" or "2.3" or "2"
so it seems to be irrelevant for me
Do you need to provide underlying data or just the rounded data?
Ie Does the value in Excel need to be 2.7777 and displayed as 2.8
or can the cell have the value 2.8 directly.
I do not realize there are two options. idealy, I would be happy to learn both two options.
My priority is the last one, "have the value 2.8 directly"/
For the second option then, round the data using the ROUND() function and then use Proc Export.
data class;
set sashelp.class;
weight=round(weight, 0.1);
height=round(height, 0.1);
run;
proc export data=class outfile='/folders/myfolders/sample.xlsx' dbms=xlsx replace; run;
Thank you very much. This works. How about the first option you mentioned,
ods tagsets.excelxp file='C:/_localdata/temp.xml' style=meadow;
proc report data=sashelp.classfit nowd;
column name age sex weight height predict;
define name/display;
define age/display;
define sex/display;
define weight/display;
define height/display;
define predict/style={tagattr='format:####.0'};
run;quit;
ods tagsets.excelxp close;
The Tagsets link I originally included has demo code showing the various features of Tagsets destination. As mentioned this does not generate a native XLSX file, but you can open it with excel or use the VBS to convert it to XLSX automatically.
If you're interested in that please read the following SAS Sample, there is code under the Download tab.
http://support.sas.com/kb/43/496.html
If you have SAS 9.4 you can try changing the destination to ODS EXCEL and see if it generates a native excel file successfully. I don't have SAS 9.4 to test on.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.