topic Re: Add numbers stored as character in SAS Enterprise Guide

Add numbers stored as character

deleted_user — Wed, 01 Sep 2010 14:57:26 GMT

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

Re: Add numbers stored as character

Cynthia_sas — Wed, 01 Sep 2010 15:06:42 GMT

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

Re: Add numbers stored as character

deleted_user — Wed, 01 Sep 2010 15:20:29 GMT

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

Re: Add numbers stored as character

deleted_user — Wed, 01 Sep 2010 17:00:21 GMT

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

Re: Add numbers stored as character

Live4SAS — Wed, 25 Jul 2012 14:52:07 GMT

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

Re: Add numbers stored as character

Dorota_Jarosz — Wed, 25 Jul 2012 15:15:29 GMT

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.

Re: Add numbers stored as character

Live4SAS — Wed, 25 Jul 2012 15:36:04 GMT

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

Re: Add numbers stored as character

Dorota_Jarosz — Wed, 25 Jul 2012 15:48:26 GMT

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?

Re: Add numbers stored as character

Live4SAS — Wed, 25 Jul 2012 15:52:31 GMT

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

Re: Add numbers stored as character

Dorota_Jarosz — Wed, 25 Jul 2012 16:35:49 GMT

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.

Re: Add numbers stored as character

Live4SAS — Wed, 25 Jul 2012 16:55:48 GMT

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

Re: Add numbers stored as character

Dorota_Jarosz — Wed, 25 Jul 2012 17:28:00 GMT

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

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