topic Re: ODS tagattr for credit value in SAS Programming

ODS tagattr for credit value

Elliott — Thu, 13 Jul 2023 15:37:36 GMT

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

Re: ODS tagattr for credit value

Reeza — Thu, 13 Jul 2023 16:00:18 GMT

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)'

Re: ODS tagattr for credit value

Elliott — Thu, 13 Jul 2023 18:32:21 GMT

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

Re: ODS tagattr for credit value

Tom — Thu, 13 Jul 2023 18:53:20 GMT

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;

Re: ODS tagattr for credit value

Elliott — Thu, 13 Jul 2023 23:53:05 GMT

Hi Tom,

Processing on Linux creating an excel output.

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

Thanks,

Elliott

Re: ODS tagattr for credit value

Tom — Fri, 14 Jul 2023 01:41:11 GMT

I am not Johnny Carson.

Please share an example of code that does not work.

Explain what about it does not work.

Re: ODS tagattr for credit value

Elliott — Fri, 14 Jul 2023 12:29:29 GMT

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

Re: ODS tagattr for credit value

JosvanderVelden — Fri, 14 Jul 2023 12:56:35 GMT

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.

Re: ODS tagattr for credit value

Tom — Fri, 14 Jul 2023 13:55:15 GMT

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.

Re: ODS tagattr for credit value

Elliott — Sat, 22 Jul 2023 11:22:24 GMT

unfortunately I was not able to get this to work.

Thanks,

Elliott

Re: ODS tagattr for credit value

ballardw — Sat, 22 Jul 2023 17:41:41 GMT

@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-data-AKA-generate/ta-p/258712 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-data-AKA-generate/ta-p/258712 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.

Re: ODS tagattr for credit value

Elliott — Sun, 30 Jul 2023 11:15:48 GMT

Thank you, I have achieved the desired result.