topic Re: PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES in SAS Programming

PERMANENT FIX TO REMOVE DOLLAR SIGN IN FRONT OF VALUES

KiranMaddi — Thu, 26 Feb 2015 19:12:27 GMT

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.

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

Reeza — Thu, 26 Feb 2015 19:34:01 GMT

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?

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

KiranMaddi — Thu, 26 Feb 2015 19:43:44 GMT

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.

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

Reeza — Thu, 26 Feb 2015 19:56:35 GMT

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;

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

KiranMaddi — Thu, 26 Feb 2015 20:01:24 GMT

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.

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

Reeza — Thu, 26 Feb 2015 20:04:25 GMT

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.

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

KiranMaddi — Thu, 26 Feb 2015 20:06:43 GMT

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.

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

csnodgrass — Mon, 24 Jul 2017 16:45:15 GMT

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.

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

Tom — Mon, 24 Jul 2017 18:48:31 GMT

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?

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

csnodgrass — Mon, 24 Jul 2017 20:12:13 GMT

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.

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

Tom — Mon, 24 Jul 2017 20:28:38 GMT

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.

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

csnodgrass — Tue, 25 Jul 2017 12:53:39 GMT

Very good ! Thanks for the additional tip

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

csnodgrass — Tue, 25 Jul 2017 13:43:52 GMT

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!

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

Tom — Tue, 25 Jul 2017 13:53:11 GMT

@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.