BookmarkSubscribeRSS Feed
KiranMaddi
Obsidian | Level 7

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.

13 REPLIES 13
Reeza
Super User

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?

KiranMaddi
Obsidian | Level 7

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.

Reeza
Super User

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;

KiranMaddi
Obsidian | Level 7

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.

Reeza
Super User

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.

KiranMaddi
Obsidian | Level 7

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.

csnodgrass
Fluorite | Level 6

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.

Tom
Super User Tom
Super User

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?

csnodgrass
Fluorite | Level 6

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.

 

 

 

 

Tom
Super User Tom
Super User

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.

csnodgrass
Fluorite | Level 6
Very good ! Thanks for the additional tip
csnodgrass
Fluorite | Level 6

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!

 

Tom
Super User Tom
Super User

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

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
  • 13 replies
  • 5384 views
  • 8 likes
  • 4 in conversation