PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

Reply
Frequent Contributor
Posts: 107

PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

CAN SOME ONE RECOMMEND ME ANY PERMANENT FIX REMOVING DOLLAR SIGN($) IN FRONT OF RECORDS(Numbers). NOT THE (INFORMAT) AS THE TABLES ARE MOVING FROM SQL TO SAS. THE VARIABLES SHOULD NOT CHANGE FROM NUMERIC TO CHARACTER.

Super User
Posts: 19,038

Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

DON'T TYPE IN ALL CAPS

What do your variables look like, are they character or numeric to start with? What format/informat and how are you moving the tables that you are having this problem?

Frequent Contributor
Posts: 107

Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

Hi Reeza,

Thanks for your prompt response. The tables were created in SQL.

Ex: The values for a variable called Invoice like

Invoice

$20,000

$3,23,000

I want those values with out $. Tried (Informat Invoice comma11.). Did not work and dollar was still there. Used a complicated process

1) compress(It converted Invoice to character)

2) then used Input( to convert them to numerice)

I know there must be a best way to do it which I am not aware of. What can we do to permanently fix this by creating a piece of code to avoid doing all those stuff above?

Sorry I am very new to SAS.

Super User
Posts: 19,038

Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

INformat controls how a variable is IMported.

Format is how it is display. Apply a format, such as BEST32. instead to see the result.

data want;

set have;

format var best32.;

run;

Frequent Contributor
Posts: 107

Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

Thanks for your reply.But I want the dollar to be removed and the variable should end up with numeric values. It is possible using compress and Input but I want a piece of code that does that job without writing code again.

Super User
Posts: 19,038

Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

Then you either need to fix it in your SQL tables or in however you bring it into SAS, which you haven't explained. 

Note that format will leave the variable as numeric and is more efficient than compress/input.

Frequent Contributor
Posts: 107

Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

Hi Reeza.That worked on a sample code that I created. Will try on the original code tomorrow and let you know.Thank you very much.

New Contributor
Posts: 4

Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

Im having trouble with this too and just found it. I understand the frustration and all caps. Ugh. Hopefully reading below will help me as well.

Super User
Super User
Posts: 6,842

Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

Post a new question/topic. Include details of what you are doing.  Are you reading from text files? Reading from SAS datasets? Data from some other file format (xlsx files perhaps)? Reading from some other external database system such as Oracle, Microsoft SQL server?

New Contributor
Posts: 4

Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

This is now solved. My data  was in a SAS dataset, and the fix below worked.
Used proc sql for this.

 

proc sql;

Create table dataset as

id,

amt1 format comma11.2,

amt2 format comma11.2

from dataset;

Quit;

 

Result:

2345.56 and 87.60 from original $87.60,$2345.56

 

In this case the original source had no bearing, as it ended up as a SAS Dataset with $Dollar14.2 and Informat 14.

 

 

 

 

Super User
Super User
Posts: 6,842

Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

[ Edited ]

So from your description you have a numeric variable that has had the DOLLAR format permanenly attached to it. Perhaps this was done automatically when you pulled the data from an external database that had an indication that the value represented money.

 

You can change the format attached to a variable without totally re-creating the dataset using PROC DATASETS.

 

proc datasets nolist lib=WORK;
  modify dataset;
  format amt1 amt2  comma11.2 ;
run;

You can also just leave the DOLLAR format along and tell SAS to use a different format when you use the data.

proc means data=dataset ;
  var amt1 ;
  format amt1 best14.;
run;

 Basically that is what you did when you used PROC SQL to re-create the dataset.

New Contributor
Posts: 4

Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

Very good ! Thanks for the additional tip
New Contributor
Posts: 4

Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

For the leaving of the format, if a dataset is exported to CSV, txt, excel or other format, does it export with original formatting or with the

"use as" formatting?
Thank!

 

Super User
Super User
Posts: 6,842

Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES


csnodgrass wrote:

For the leaving of the format, if a dataset is exported to CSV, txt, excel or other format, does it export with original formatting or with the

"use as" formatting?
Thank!

 


Depends on the method used to "export".  If you are using something like data step to write a CSV file then it should use the format when writing the value.  Similarly if you are using ODS EXCEL plus PROC PRINT to generate an EXCEL file then the formats will apply.  

Ask a Question
Discussion stats
  • 13 replies
  • 1238 views
  • 7 likes
  • 4 in conversation