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

dollarw.d formatting in data _null_ with negative numbers

buddha_d — Thu, 31 Aug 2017 04:24:39 GMT

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;

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

RahulG — Thu, 31 Aug 2017 04:50:25 GMT

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

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

buddha_d — Thu, 31 Aug 2017 04:53:24 GMT

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.

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

ChrisNZ — Thu, 31 Aug 2017 05:00:06 GMT

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)

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

ChrisNZ — Thu, 31 Aug 2017 05:01:51 GMT

Open the CSV file in Notepad, not Excel.

*Never* check raw data in Excel.

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

Tom — Thu, 31 Aug 2017 06:43:37 GMT

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

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

buddha_d — Thu, 31 Aug 2017 09:07:04 GMT

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.

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

RahulG — Thu, 31 Aug 2017 11:01:27 GMT

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.

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

Tom — Thu, 31 Aug 2017 14:27:43 GMT

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.

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

ballardw — Thu, 31 Aug 2017 18:46:45 GMT

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.