Hi I have a table with decimal places, The field is text and I want to remove the decimal places which have no values. See examples below
Current Table
Field_1
22.5
1.000
40.000
1.25
I would like the table to look as per below, your help in this matter is much appreciated
Field_1
22.5
1
40
1.25
data have;
input Field_1 $;
want=prxchange('s/\.0+\s*$//',1,field_1);
cards;
22.5
1.000
40.000
1.25
;
data have;
input Field_1 $;
datalines;
22.5
1.000
40.000
1.25
;
data want;
set have;
Field_2=put(input(Field_1, best8.), best8. -l);
run;
@RM6 wrote:
use strip(put(input(field_1,8.2),best.))
Your use of the 8.2 informat is not needed and actually dangerous:
data have;
input field1 :$10.;
datalines;
22.5
1.000
40.000
1.25
4000
;
data want;
set have;
field2 = strip(put(input(field1,8.2),best.));
run;
Take a close look at the last observation.
8. (or simply best.) is sufficient.
data have;
input Field_1 $;
want=prxchange('s/\.0+\s*$//',1,field_1);
cards;
22.5
1.000
40.000
1.25
;
Treating it as a character operation only:
data want;
set have;
field2 = scan(field1,1,'.');
run;
@zdassu You are trying to mix two formats: 1st and 4th row have decimal places while 2nd and 3rd don't have a decimal. For the entire column/variable you will have to settle down to one choice: with or without decimal. I am not sure if you can choose differently for different rows. So either you decide between 8 and 8.2 (without and with 2 decimals respectively) or let SAS decide with best. option.
Either ways, if I am not wrong, all rows have to conform to one standard/format for that particular column. If I am wrong please correct me.
I'll bite...why are you storing numeric data as character text? I suspect that's how your data is now, or how it is provided to you. But IMO if you stored or converted your text data to numeric, then used formats for display, reporting, etc., life would be easier.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.