topic Re: proc export preserve trailing zeros while exporting in ODS and Base Reporting

proc export preserve trailing zeros while exporting

chennupriya — Tue, 29 Mar 2016 16:09:01 GMT

I am using an export macro to export sas tables to excel

PROC EXPORT DATA= life

OUTFILE= ""C:\users/price.xlsx"

DBMS=xlsx replace;

SHEET= "time"

RUN;

but the data in the dataset for variable y = 1.0000 and y=0.9500

but when exporting into excel the values of y=1 and y=0.95 but i want to preserve zeros

can anyone pls help

Re: proc export preserve trailing zeros while exporting

ballardw — Tue, 29 Mar 2016 16:14:17 GMT

The trailing zeroes are ignored by Excel unless you force the appearance to use more decimal places. So change the column numeric format in Excel to include 4 decimal places.

Re: proc export preserve trailing zeros while exporting

chennupriya — Tue, 29 Mar 2016 16:20:41 GMT

Hi,

I have to do that for 25 sheets everytime i run for each pricing region so i want the data y to load as 1.0000 when loading from sas to excel pls help if any solution

Re: proc export preserve trailing zeros while exporting

ballardw — Tue, 29 Mar 2016 16:42:28 GMT

Proc Export has no way to provide any format information. It is intended as a data value interchange so appearance is not a major consideration.

You might try Proc Print using ODS Tagsets.excelxp and the tagattr options to tell Excel to read it as a string (character). However this will require setting that option for every single variable involved.

What is done with the values once they are in Excel?

Re: proc export preserve trailing zeros while exporting

Reeza — Tue, 29 Mar 2016 16:48:09 GMT

Given you're generating multiple sheets, I think ODS EXCEL or ODS TAGSETS are viable solutions, but it may be cumbersome depending on your data structure.

Another option is CSV files, but excel will probably truncate it upon reading. Its helpfulness is not very helpful.

Re: proc export preserve trailing zeros while exporting

chennupriya — Tue, 29 Mar 2016 17:02:50 GMT

ods tagests.excelxp file="C:\users/price.xlsx"

style=statistical style=styles.sansprinter;

ods tagsets.excelxp options(sheet_label="time");

proc report data=Driver_Disc_Def;

column xx y;

define y / display format=10.4;

run;

ODS tagsets.ExcelXP CLOSE;

when i run this i get an error as follows

ERROR: No body file. TAGSETS.EXCELXP output will not be created.

CAN ANYONE PLS HELP

Re: proc export preserve trailing zeros while exporting

chennupriya — Tue, 29 Mar 2016 17:04:52 GMT

just want to inform i am using excel 2013

Re: proc export preserve trailing zeros while exporting

Reeza — Tue, 29 Mar 2016 17:04:53 GMT

That can't be the only error...look at your path, you have both forward and backward slashes.

Also, you have tow style definitions.

Look at the examples from Cynthia Zender or the papers. You can't specify format using format statement for ODS you need to use TAGATTR. Google it, or search on here, there's a ton of working examples.

Re: proc export preserve trailing zeros while exporting

Reeza — Tue, 29 Mar 2016 17:06:40 GMT

https://communities.sas.com/t5/ODS-and-Base-Reporting/Excel-changing-decimal-value-rounding-perhaps/m-p/230595/highlight/true#M14527

Here's a fully worked example with decimal values specified.

Re: proc export preserve trailing zeros while exporting

chennupriya — Tue, 29 Mar 2016 17:30:38 GMT

ods _all_ close;

ods listing close;

ods tagests.excelxp path ="C:\users"

file = "price.xls" style=minimal;

ods tagsets.excelxp

options(sheet_name= 'time'

frozen_headers="yes"

absolute_column_width='8.75, 6.25, 10.57, 7.43, 15, 8.14, 30.86, 10.57'

autofit_height='yes');

proc report data=Driver_Disc_Def nowd

STYLE(REPORT) = [BACKGROUND=WHITE FOREGROUND=BLACK]

STYLE(COLUMN) = [BACKGROUND=WHITE FOREGROUND=BLACK FONT_SIZE=2]

STYLE(HEADER) = [BACKGROUND=WHITE FOREGROUND=BLUE BOLD FONT_SIZE=2];

columns xx yy ;

define yy /'yy' display style={tagattr='format:##0.0000'};

run;

ods _all_ close;

still same error

22 GOPTIONS ACCESSIBLE;

23 ods _all_ close;

24 ods listing close;

25 ods tagests.excelxp path="C:\users"

26 file = "price.xls" style=minimal;

WARNING: Tagset TAGESTS.EXCELXP not found; the default tagset will be used instead.

NOTE: Writing TAGSETS.DEFAULT Body file: price.xls

28 ods tagsets.excelxp

29 options(sheet_name= "time"

30 frozen_headers="yes"

32 absolute_column_width='8.75, 6.25, 10.57, 7.43, 15, 8.14, 30.86, 10.57'

34 autofit_height='yes');

ERROR: No body file. TAGSETS.EXCELXP output will not be created.

35 proc report data=gg nowd

36 STYLE(REPORT) = [BACKGROUND=WHITE FOREGROUND=BLACK]

38 STYLE(COLUMN) = [BACKGROUND=WHITE FOREGROUND=BLACK FONT_SIZE=2]

40 STYLE(HEADER) = [BACKGROUND=WHITE FOREGROUND=BLUE BOLD FONT_SIZE=2];

42 columns xx yy;

43 define yy / 'yy' display style={tagattr='format:##0.0000'};

44 run;

Re: proc export preserve trailing zeros while exporting

Reeza — Tue, 29 Mar 2016 17:32:49 GMT

WARNING: Tagset TAGESTS.EXCELXP not found; the default tagset will be used instead.

Spelling mistake.

Tagsets versus Tagests...

Re: proc export preserve trailing zeros while exporting

chennupriya — Tue, 29 Mar 2016 17:51:50 GMT

Thank you got it