The SAS Output Delivery System and reporting techniques

proc export preserve trailing zeros while exporting

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

proc export preserve trailing zeros while exporting

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

 


Accepted Solutions
Solution
‎03-29-2016 01:51 PM
Super User
Posts: 19,057

Re: proc export preserve trailing zeros while exporting

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

 

Spelling mistake.

Tagsets versus Tagests...

View solution in original post


All Replies
Super User
Posts: 11,114

Re: proc export preserve trailing zeros while exporting

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.

Frequent Contributor
Posts: 123

Re: proc export preserve trailing zeros while exporting

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

Super User
Posts: 11,114

Re: proc export preserve trailing zeros while exporting

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?

Super User
Posts: 19,057

Re: proc export preserve trailing zeros while exporting

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.

 

Frequent Contributor
Posts: 123

Re: proc export preserve trailing zeros while exporting

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

Frequent Contributor
Posts: 123

Re: proc export preserve trailing zeros while exporting

just want to inform i am using excel 2013

Super User
Posts: 19,057

Re: proc export preserve trailing zeros while exporting

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. 

 

Super User
Posts: 19,057

Re: proc export preserve trailing zeros while exporting

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.

Frequent Contributor
Posts: 123

Re: proc export preserve trailing zeros while exporting

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;

 

Solution
‎03-29-2016 01:51 PM
Super User
Posts: 19,057

Re: proc export preserve trailing zeros while exporting

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

 

Spelling mistake.

Tagsets versus Tagests...

Frequent Contributor
Posts: 123

Re: proc export preserve trailing zeros while exporting

Thank you got it

☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 1023 views
  • 0 likes
  • 3 in conversation