BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Elliott
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@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.

View solution in original post

11 REPLIES 11
Reeza
Super User
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
Obsidian | Level 7

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

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;

Tom_0-1689274378568.png

 

 

Elliott
Obsidian | Level 7

Hi Tom,

Processing on Linux creating an excel output.

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

 

Thanks,

Elliott

 

Tom
Super User Tom
Super User

Tom_0-1689298826972.jpeg

I am not Johnny Carson.

 

Please share an example of code that does not work.

Explain what about it does not work.

Elliott
Obsidian | Level 7
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
Super User Tom
Super User

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
SAS Super FREQ
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
Obsidian | Level 7

unfortunately I was not able to get this to work.

 

Thanks,

Elliott

ballardw
Super User

@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
Obsidian | Level 7

Thank you, I have achieved the desired result.  

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1097 views
  • 0 likes
  • 5 in conversation