08-31-2017 12:24 AM
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.
input 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
proc sql noprint;
%if &nobs ne 0 %then %do;
format money1 money2 money3 dollar25.2;
if _n_=1 then do;
put name ',' money1 ',' money2 ',' money3;
08-31-2017 12:50 AM
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
08-31-2017 12:53 AM
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.
08-31-2017 01:00 AM
You should not get parentheses with the dollar format.
data _null_; A=-1.1; putlog A= dollar25.2 A= negparen25.2; run;
08-31-2017 02:43 AM
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
08-31-2017 05:07 AM
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.
08-31-2017 07:01 AM
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.
08-31-2017 10:27 AM
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.
08-31-2017 02:46 PM
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.