DATA Step, Macro, Functions and more

PROC EXPORT AND FORMAT

Accepted Solution Solved
Reply
Super Contributor
Posts: 345
Accepted Solution

PROC EXPORT AND FORMAT

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

 


Accepted Solutions
Solution
‎01-19-2016 08:18 AM
Super User
Posts: 19,770

Re: PROC EXPORT AND FORMAT

[ Edited ]

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


All Replies
Super User
Posts: 3,250

Re: PROC EXPORT AND FORMAT

can I use format to do that? - Yes.

 

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

Trusted Advisor
Posts: 1,117

Re: PROC EXPORT AND FORMAT

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.

Super Contributor
Posts: 345

Re: PROC EXPORT AND FORMAT

Posted in reply to FreelanceReinhard

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

Super User
Posts: 19,770

Re: PROC EXPORT AND FORMAT

What version of SAS do you have?

How big is the dataset?

Super Contributor
Posts: 345

Re: PROC EXPORT AND FORMAT

SAS 9.4

it has over 100000 obs

Trusted Advisor
Posts: 1,117

Re: PROC EXPORT AND FORMAT

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.

Super Contributor
Posts: 345

Re: PROC EXPORT AND FORMAT

Posted in reply to FreelanceReinhard

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

Super User
Posts: 19,770

Re: PROC EXPORT AND FORMAT

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

 

Super Contributor
Posts: 345

Re: PROC EXPORT AND FORMAT

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

Super User
Posts: 19,770

Re: PROC EXPORT AND FORMAT

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.

Super Contributor
Posts: 345

Re: PROC EXPORT AND FORMAT

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

Solution
‎01-19-2016 08:18 AM
Super User
Posts: 19,770

Re: PROC EXPORT AND FORMAT

[ Edited ]

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;

 

Super Contributor
Posts: 345

Re: PROC EXPORT AND FORMAT

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

Super User
Posts: 19,770

Re: PROC EXPORT AND FORMAT

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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