BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
11 REPLIES 11
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
deleted_user
Not applicable
Also consider adding 0 as an argument to the sum function. This helps if all your four variables are missing.
Dorota_Jarosz
Obsidian | Level 7

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

Live4SAS
Calcite | Level 5

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

Dorota_Jarosz
Obsidian | Level 7

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.

Live4SAS
Calcite | Level 5

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

Dorota_Jarosz
Obsidian | Level 7

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?

Live4SAS
Calcite | Level 5

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

Dorota_Jarosz
Obsidian | Level 7

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.

Live4SAS
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 11 replies
  • 1831 views
  • 1 like
  • 4 in conversation