BookmarkSubscribeRSS Feed
HN2001
Obsidian | Level 7

I have a proc print statement within an ods excel statement.  When I view the dataset, I see the second variable has a comma present in the values (See ViewofDataGoingIntoProcPrintStatement.JPG) 

When I run the code and view the proc print output in SAS I see that the second variable has a comma in the value just as I viewed it in the data (See ViewofProcPrint_OutputWindow.JPG)

However, when I view the excel file that second variable does not maintain the commas (See Excel.JPG)

It should be noted that the first variable is formatted is the same as the second.  Both variables are character.

The first variable has a comma in the data in sas and also in excel but the second variable does not maintain the comma.

 

Has anyone ever encountered a comma not being carried over into excel when you are using proc print within an ods excel statement?

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

Hi:
Excel does not always respect the SAS format when you create output from PROC PRINT using ODS EXCEL (or any of the ODS destinations that create output for Excel).

There are 2 ways to ensure that your format will be respected. For some formats (like date formats), explicitly specifying a SAS format will cause the ODS EXCEL destination to "translate" the SAS format to the equivalent Microsoft format for Excel.

Otherwise, you need to use the TAGATTR style override to specify a Microsoft-specific format for Excel to use, as described in this paper: https://support.sas.com/resources/papers/proceedings11/266-2011.pdf -- this paper was written before ODS EXCEL, but the syntax shown for ODS TAGSETS.EXCELXP will be almost the same. You do have to use the construction as shown on page 3 for ISBN:
define isbn / display
style(column)={tagattr='Format:@'}; (to specify a character format should be used for a PROC REPORT variaable)

or like this:
var numvar/
style(data)={tagattr='Format:###,##0.00'}; (for PROC PRINT to specify comma and decimal places)

 

The paper has more details and explanations of the Microsoft formats.

cynthia

HN2001
Obsidian | Level 7

thanks Cynthia.  I wasn't using the tagsets but ended up formatting the variable again to make sure it was character and it worked.

thanks again for the idea of formatting.

ballardw
Super User

No data and no code makes it extremely hard to diagnose programming elements.

Pictures may provide output but we can't see any detail of how that output was generated.

 

Excel is notorious for "helping" display values using an Excel display different than expected. If you did not use style elements in proc print to control Excel formats the results can be problematic as Excel "picks" a display format. Perhaps it is turning values into numeric and displaying them with a no comma format? Always check Excel cell attributes in Excel to see if they make sense.

HN2001
Obsidian | Level 7
Apologies for the lack of data and code, I felt it was hard for me to make the code generic and still protect the confidentiality of the data I was working with.

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
  • 4 replies
  • 1487 views
  • 0 likes
  • 3 in conversation