proc export preserve trailing zeros while exporting

[chennupriya]

Hi

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

[ballardw]

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.

[chennupriya]

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

[ballardw]

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?

[Reeza]

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.

[chennupriya]

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

[chennupriya]

just want to inform i am using excel 2013

[Reeza]

https://communities.sas.com/t5/ODS-and-Base-Reporting/Excel-changing-decimal-value-rounding-perhaps/...

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

[Reeza]

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.

[chennupriya]

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

27

28 ods tagsets.excelxp

29 options(sheet_name= "time"

30 frozen_headers="yes"

31

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

33

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]

37

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

39

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

41

42 columns xx yy;

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

44 run;

[Reeza · Accepted solution]

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

Spelling mistake.

Tagsets versus Tagests...

[chennupriya]

Thank you got it