topic Re: PROC EXPORT AND FORMAT in SAS Programming

PROC EXPORT AND FORMAT

Bal23 — Mon, 18 Jan 2016 17:00:25 GMT

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

Re: PROC EXPORT AND FORMAT

SASKiwi — Mon, 18 Jan 2016 18:28:40 GMT

can I use format to do that? - Yes.

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

Re: PROC EXPORT AND FORMAT

FreelanceReinh — Mon, 18 Jan 2016 18:29:10 GMT

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.

Re: PROC EXPORT AND FORMAT

Bal23 — Mon, 18 Jan 2016 18:35:39 GMT

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

Re: PROC EXPORT AND FORMAT

Reeza — Mon, 18 Jan 2016 19:15:28 GMT

What version of SAS do you have?

How big is the dataset?

Re: PROC EXPORT AND FORMAT

FreelanceReinh — Mon, 18 Jan 2016 19:15:48 GMT

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.

Re: PROC EXPORT AND FORMAT

Reeza — Mon, 18 Jan 2016 19:30:38 GMT

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-from-sas/

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

Re: PROC EXPORT AND FORMAT

Bal23 — Mon, 18 Jan 2016 19:57:58 GMT

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

Re: PROC EXPORT AND FORMAT

Bal23 — Mon, 18 Jan 2016 19:58:50 GMT

SAS 9.4

it has over 100000 obs

Re: PROC EXPORT AND FORMAT

Bal23 — Mon, 18 Jan 2016 20:00:50 GMT

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

Re: PROC EXPORT AND FORMAT

Reeza — Mon, 18 Jan 2016 20:09:17 GMT

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.

Re: PROC EXPORT AND FORMAT

Bal23 — Mon, 18 Jan 2016 20:13:09 GMT

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

Re: PROC EXPORT AND FORMAT

Reeza — Mon, 18 Jan 2016 20:19:46 GMT

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;

Re: PROC EXPORT AND FORMAT

Bal23 — Mon, 18 Jan 2016 20:31:17 GMT

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

Re: PROC EXPORT AND FORMAT

Reeza — Mon, 18 Jan 2016 20:51:47 GMT

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.

Re: PROC EXPORT AND FORMAT

Bal23 — Tue, 19 Jan 2016 16:53:05 GMT

Thank you. I did try this option. The problem is, the result is generated automatically omited 0 infront.

that is I want it shows as "0.97" but it shows ".97"

can it be fixed?

Re: PROC EXPORT AND FORMAT

Reeza — Tue, 19 Jan 2016 17:28:32 GMT

Figure out the Excel format you need and put it in the tagattr , ie 0####.0.

Re: PROC EXPORT AND FORMAT

Bal23 — Tue, 19 Jan 2016 17:34:08 GMT

Thanks. It works!