BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Bal23
Lapis Lazuli | Level 10

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?
------

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

 

View solution in original post

17 REPLIES 17
SASKiwi
PROC Star

can I use format to do that? - Yes.

 

Any other options? - No, not with PROC EXPORT.

FreelanceReinh
Jade | Level 19

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.

Bal23
Lapis Lazuli | Level 10

Would you please show me a sample excel macro code? Thanks

Reeza
Super User

What version of SAS do you have?

How big is the dataset?

Bal23
Lapis Lazuli | Level 10

SAS 9.4

it has over 100000 obs

FreelanceReinh
Jade | Level 19

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.

Bal23
Lapis Lazuli | Level 10

Thank you. This is a little hard for me to understand

Reeza
Super User

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

http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets...

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

 

Bal23
Lapis Lazuli | Level 10

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

Reeza
Super User

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.

Bal23
Lapis Lazuli | Level 10

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"/

Reeza
Super User

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;

 

Bal23
Lapis Lazuli | Level 10

Thank you very much. This works. How about the first option you mentioned,

Reeza
Super User
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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 8175 views
  • 3 likes
  • 4 in conversation