BookmarkSubscribeRSS Feed
buddha_d
Pyrite | Level 9

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;

9 REPLIES 9
RahulG
Barite | Level 11

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

buddha_d
Pyrite | Level 9

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. 

ChrisNZ
Tourmaline | Level 20

Open the CSV file in Notepad, not Excel.

*Never* check raw data in Excel.

ChrisNZ
Tourmaline | Level 20

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)

 

Tom
Super User Tom
Super User

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
buddha_d
Pyrite | Level 9

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. 

RahulG
Barite | Level 11

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. 

 

 

Tom
Super User Tom
Super User

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.

ballardw
Super User

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.

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
  • 9 replies
  • 1504 views
  • 0 likes
  • 5 in conversation