DATA Step, Macro, Functions and more

dollarw.d formatting in data _null_ with negative numbers

Reply
Frequent Contributor
Posts: 100

dollarw.d formatting in data _null_ with negative numbers

SAS gurus,

            I ran the below program and I am attaching output as well. But what I don't want to see is () for dollar format (for - ve money )  in the output  .csv file. I want to see the same numbers in the output (.csv file) with dollar sign before them for example -158.57 should look like $ -158.57 or - $ 158.57 (I don't know if its a possibility). If you need further clarification, please ask.

Any ideas how to fix this. 

 

data new;
input name$ money1 money2 money3;
cards;
ryan 125.12 -158.57 147.21
peter 128.74 584.17 354.00
smith -152.00 259.00 325.00
;
run;

 

proc sql noprint;
select count(*)
into :NObs
from work.new;

 


%macro money;
%if &nobs ne 0 %then %do;
data _null_;
file 'C:\Intel\new.csv';
set new;
format money1 money2 money3 dollar25.2;
if _n_=1 then do;
put;
put 'name,money1,money2,money3';
put;
end;
put name ',' money1 ',' money2 ',' money3;
run;
%end;
%mend;
%money;

Super Contributor
Posts: 266

Re: dollarw.d formatting in data _null_ with negative numbers

If I open your output file new.csv in text pad. Values are written as you want. 

name,money1,money2,money3

ryan ,$125.12 ,$-158.57 ,$147.21
peter ,$128.74 ,$584.17 ,$354.00
smith ,$-152.00 ,$259.00 ,$325.00

 

 () is displayed in MS excel. You may have to change Cell format to view as you like

Frequent Contributor
Posts: 100

Re: dollarw.d formatting in data _null_ with negative numbers

Rahul, Thank you for your prompt reply. I want my code to produce that (I know I could manipulate to look the way I want it to look). There must be a way to code it around. I am trying to find that out. 

PROC Star
Posts: 1,760

Re: dollarw.d formatting in data _null_ with negative numbers

Open the CSV file in Notepad, not Excel.

*Never* check raw data in Excel.

PROC Star
Posts: 1,760

Re: dollarw.d formatting in data _null_ with negative numbers

You should not get parentheses with the dollar format.


data _null_; 
  A=-1.1; 
  putlog  A= dollar25.2  A= negparen25.2; 
run;

A=$-1.10 A=(1.10)

 

Super User
Super User
Posts: 7,060

Re: dollarw.d formatting in data _null_ with negative numbers

The DOLLAR format seems to do what you want.  How did you view the CSV file?

595   data _null_;
596     file log dsd ;
597     set new ;
598     format money: dollar25.2 ;
599     put (name money:) (+0) ;
600   run;

ryan,$125.12,$-158.57,$147.21
peter,$128.74,$584.17,$354.00
smith,$-152.00,$259.00,$325.00
Frequent Contributor
Posts: 100

Re: dollarw.d formatting in data _null_ with negative numbers

Tom, I do see the format for the negative numbers with your code, but the numbers don't get separated in individual cells (in excel). 

I am using excel to view it as this is the standard practice. So could you please make changes in my code and send it back to me. I would really appreciate it. Thanks in advance. 

Super Contributor
Posts: 266

Re: dollarw.d formatting in data _null_ with negative numbers

What I understand, it is related to the way you see the data in excel. SAS is producing the result as desired but you need to check the cell format in excel. 

 

 

Super User
Super User
Posts: 7,060

Re: dollarw.d formatting in data _null_ with negative numbers

A CSV file has no place to store formatting information.  You will probably need to post on a Excel forum if you want help with changing how Excel handles CSV files.

 

Or you could change your SAS code to generate an Excel file directly instead.

If you use ODS EXCEL and PROC PRINT to create the file they you can attach Excel formats to the variables.

Super User
Posts: 11,343

Re: dollarw.d formatting in data _null_ with negative numbers

If you must look at the data in Excel and want a $ AND no () for negative numbers you will either have create a custom Excel cell format display as none of the Excel currency or custom formats is set to display any -$ or $-.

 

Remember that Excel comes from an accounting background (spreadsheets existed long before computers) and those folks have there standard displays and $ and - don't mix.

Ask a Question
Discussion stats
  • 9 replies
  • 146 views
  • 0 likes
  • 5 in conversation