BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chennupriya
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

 

Spelling mistake.

Tagsets versus Tagests...

View solution in original post

11 REPLIES 11
ballardw
Super User

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
Quartz | Level 8

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
Super User

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
Super User

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
Quartz | Level 8

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
Quartz | Level 8

just want to inform i am using excel 2013

Reeza
Super User

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
Quartz | Level 8

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
Super User

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

 

Spelling mistake.

Tagsets versus Tagests...

chennupriya
Quartz | Level 8

Thank you got it

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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