ODS tagattr for credit value

[Elliott]

I have a user asking to reformat a $ value when it is a credit it is showing as $-1,175.55 user wants ($1,175.55)

I am using :

style(column)= [tagattr='format:$#,###,##0.00']; = $-1,175.55

The users want the value to look like ($1,175.55)

Is there a tagattr style that will give me that?

Thanks,

Elliott

[Reeza]

Is this for Excel? If so, you need the Excel format?
https://www.ablebits.com/office-addins-blog/custom-excel-number-format/

Possibly: 'format:$#,###,##0.00;($#,###,##0.00)'

[Elliott]

Hi Reeza,

Unfortunately, this did not work.  I am outputting to excel and we are processing on a linux server.

Thank you for the link, that was very helpful.

is there anything else to try?

Thanks,

Elliott

[Tom]

Processing what on Linux?  The EXCEL file?  or the SAS program that is writing the EXCEL file?

Setting the format works for me:

missing d;
data test;
  input id amount ;
cards;
1 1234.56
2 -1234.56
3 0
4 D
;

ods excel file='c:\downloads\credit.xlsx';
proc print data=test;
  id id;
  var amount / style(data)=[tagattr='format:$#,###,##0.00;($#,###,##0.00);"-";[Red]@'] ;
run;
ods excel close;

[Elliott]

Hi Tom,

Processing on Linux creating an excel output.

I tried your suggestion but it did not give the desired result.

Thanks,

Elliott

[Tom]

I am not Johnny Carson.

Please share an example of code that does not work.

Explain what about it does not work.

[Elliott]

Hi Tom,

The problem is that the user wants the excel output to show the credit value as ($1,755.50) not $-1,755.50.

I am using -- Cur_Bal / style(Column)= [tagattr='format:$#,###,##0.00'];  -- for formatting, I have tried all suggestions and they have not given the result.

Here is my code:

ods tagsets.excelxp

options(embedded_titles='yes' minimize_style='yes' wraptext='no' AUTOFIT_HEIGHT='yes' )

rs=none style=XLsansPrinter;

ods noproctitle;

/*Sample tab*/

ods tagsets.excelxp options(sheet_name="Inventory_Mismatch" sheet_interval='none' embedded_titles='yes' wraptext = "no" autofilter='all'

                            absolute_column_width="15,15,15,15,20,25,15");  

proc print data=BASE_1 label Noobs;

var

IDNumber /style(Column)= [tagattr='format:text' ];

var

Status_Dt

GroupCode;

var

Cur_Bal / style(Column)= [tagattr='format:$#,###,##0.00'];

var

Category

;

run;

[Tom]

You did not include the extra values in the Excel format so that it Excel would know you wanted the negative values formatted differently.

var Cur_Bal
  / style(Column)= [tagattr='format:$#,###,##0.00;($#,###,##0.00)']
;

Also, you are not making a real Excel file.  You are making an XML file.  To write an actual XLSX file use ODS EXCEL instead of the EXCELXP tagset.

[JosvanderVelden]

I tried Tom's code on SAS OnDemand for Academics (=linux server) and got the desired result. The SAS Studio results window shows -1234.56. But the excel file is formatted correctly. I downloaded the excel file and opened it to verify.

[Elliott]

unfortunately I was not able to get this to work.

Thanks,

Elliott

[ballardw · Accepted solution]

---

@Elliott wrote:

unfortunately I was not able to get this to work.

Thanks,

Elliott

---

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

[Elliott]

Thank you, I have achieved the desired result.