Commas not being maintained from sas proc print to excel

Reply
Occasional Contributor
Posts: 12

Commas not being maintained from sas proc print to excel

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?

SAS Super FREQ
Posts: 9,373

Re: Commas not being maintained from sas proc print to excel

[ Edited ]

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

Occasional Contributor
Posts: 12

Re: Commas not being maintained from sas proc print to excel

Posted in reply to Cynthia_sas

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.

Super User
Posts: 13,583

Re: Commas not being maintained from sas proc print to excel

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.

Occasional Contributor
Posts: 12

Re: Commas not being maintained from sas proc print to excel

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.
Ask a Question
Discussion stats
  • 4 replies
  • 77 views
  • 0 likes
  • 3 in conversation