Desktop productivity for business analysts and programmers

Add numbers stored as character

Reply
N/A
Posts: 0

Add numbers stored as character

Hello All,
I have 4 character fields which has numbers stored in them and I want to add these 4 fields, so I can get a total value. I am using the input finction to convert from character to numeric and then adding the values. This works fine if all the 4 fields have a value, but I am getting a null value for my total field if any one of the 4 fields is blank. So my question is how do I add these values to get a total.


here is an example

f1 f2 f3 f4 total
1 2 3 4 10
1 4 . (I would like to get the value 5 here but for some reason I am getting .)

Here is the formula I am using

input(f1,8.)+input(f2,8.)+input(f3,8.)+input(f4,8.)

Thanks
Shri
SAS Super FREQ
Posts: 8,720

Re: Add numbers stored as character

Hi:
Look at the SUM function. With missing values, you get different results from the SUM function than you do when you use arithmetic operators -- that's because the SUM function can ignore missing values.

cynthia
N/A
Posts: 0

Re: Add numbers stored as character

Cynthia,
Thank you for your response, I did find the Sum function as soon as I posted this message. Yes the SUM function did ignore the missing values.


Thank You again
Shri
N/A
Posts: 0

Re: Add numbers stored as character

Also consider adding 0 as an argument to the sum function. This helps if all your four variables are missing.
Contributor
Posts: 66

Re: Add numbers stored as character

Hi Shri,

If you did not find a correct answer here is one more tip:

data have2;

format f1 f2 $30. fn1 8.2 fn2 tot tot2 8.2 ;

input f1 1-11 f2 12-22;

fn1=input(substr(f1,1,length(compress(f1))),best8.);

fn2=input(substr(f2,1,length(compress(f2))),best8.);

tot=sum(0,fn1,fn2);

* if you have many fni columns you may use an array instead;

array f  {*} fn1 -- fn2;

tot2=0;

do i=1 to dim(f);

tot2=sum(0,tot2,f);

end;

drop i;

cards;

1          229.00       

2          39.83        

3          38.25        

4                       

1          346.00       

           26.75        

1          55.64        

                        

;

run;

options nocenter;

proc print; run;

*Output:

Obs         fn1         fn2         tot        tot2

1         1.00      229.00      230.00      230.00

2         2.00       39.83       41.83       41.83

3         3.00       38.25       41.25       41.25

4         4.00         .          4.00        4.00

5         1.00      346.00      347.00      347.00

6          .         26.75       26.75       26.75

7         1.00       55.64       56.64       56.64

8          .           .          0.00        0.00

;

Hope this helps,

Dorota

New Contributor
Posts: 4

Re: Add numbers stored as character

Hello Cynthia i have a problem which might not be much relevant to this post, but it has some issues with Input function.

I have a character column f1 of length 30 with values nulls and single digit values 1,2,3,1,1, and another character column f2 of length 30 with values 229.00, 39.83, 38.25,346.00,55.64

when i use the input function on these columns, f1n=input(f1,8.2), f2n=input(f2,8.2), i am getting right results for column f2, but getting all null values for column f1 do you know the reason, and also for f21 i do not see 0s after the decimal.

f1          f2            f1n          f2n

1          229.00     .              229

2          39.83       .              39.83

3          38.25       .              38.25

1          346.00     .              346

1          55.64       .              55.64

Thanks

Contributor
Posts: 66

Re: Add numbers stored as character

SAS assigns the best16. format, if none is specified. Try adding a format statement for the new variables.

format f1n f2n 8.2;

f1n=sum(0,input(f1,8.2);

By the way, do you really need the character variables to be 30 characters long?  Unless you know you have very long strings (which are supposed to be numbers anyway), you should make these fields shorter.

New Contributor
Posts: 4

Re: Add numbers stored as character

I tried adding format statement, but still i am getting the same problem with the column f1.

Actually i am importing the data from a csv file and the column f1 is the last column, is there any problem with end of line character? becaue i tried using the substr function as below

f1n = input(substr(f1,1,1),8.2); and now it wont give me a null value but instead a numeric value, but the problem is this works fine if the f1 variable is a single digit character variable but it is not. so can you please let me know what can be the problem with end of line/record, and how can we remove this problem.

thanks

Contributor
Posts: 66

Re: Add numbers stored as character

To make your input function flexible, use the length function as the second parameter:

f1n = input(substr(f1,1,length(f1),8.2);

Please provide more info. How are you importing the csv file? Do you use the missover option in the filename statement?

New Contributor
Posts: 4

Re: Add numbers stored as character

yes i am using the MISSOVER option, by the way the below statement worked.

f1n=input(substr(f1,1,length(trim(f1))-1),8.2);

thanks

Contributor
Posts: 66

Re: Add numbers stored as character

Shri, (actually this was an attempt to answer RV's question)

I am getting an error with the length-1 third argument and I should.  What is even more odd, I am getting single digit hundredths values rather than integers. I guess I have to study the input function :smileyshocked:

data have;

format f1 f2 $30. f1n 8.2 f2n 8.2 ;

input f1 1-11 f2 12-22;

f1n=input(substr(f1,1,length(trim(f1))),8.2);

f2n=input(substr(f2,1,length(trim(f2))),8.2);

cards;

1          229.00       

2          39.83        

3          38.25        

1          346.00       

1          55.64        

;

run;

options nocenter;

proc print; run;

*Output: f1n values in decimals?

Obs    f1                                f2                                     f1n         f2n

1     1                                 229.00                                0.01      229.00

2     2                                 39.83                                 0.02       39.83

3     3                                 38.25                                 0.03       38.25

4     1                                 346.00                                0.01      346.00

5     1                                 55.64                                 0.01       55.64

;

data have2;

format f1 f2 $30. f1n 8.2 f2n 8.2 ;

input f1 1-11 f2 12-22;

f1n=input(substr(f1,1,length(compress(f1))),best8.);

f2n=input(substr(f2,1,length(compress(f2))),best8.);

cards;

1          229.00       

2          39.83        

3          38.25        

1          346.00       

1          55.64        

;

run;

options nocenter;

proc print; run;

*Output:

Obs    f1                                f2                                     f1n         f2n

1     1                                 229.00                                1.00      229.00

2     2                                 39.83                                 2.00       39.83

3     3                                 38.25                                 3.00       38.25

4     1                                 346.00                                1.00      346.00

5     1                                 55.64                                 1.00       55.64

;

Message was edited by: Dorota Jarosz - added (actually this was an attempt to answer RV's question) to the greetings.

New Contributor
Posts: 4

Re: Add numbers stored as character

hello Jerosz,

First of all I am not sure if this question is for Shri, but I am answering this.

I would suggest you, checking the parenthesis again. and second thing is you are getting the data using cards statement, whereas i imported the data from an external csv file, which had some issue with end of line character, so i had to use the length - 1 argument in the input and substr functions.

thanks

RV

Ask a Question
Discussion stats
  • 11 replies
  • 264 views
  • 1 like
  • 4 in conversation