## How to calculate sum across a row in SAS VA having missing values ?

Solved
Occasional Contributor
Posts: 12

# How to calculate sum across a row in SAS VA having missing values ?

[ Edited ]

I have table like this :

TABLE

CLOSED COMMIT PIPELINE

100          50            10

.               60             70

50            .                100

I want to calculate another column total = closed+commit+pipeline  in VA calculated items . How to deal with the missing values , do we have any functuion like "SUM" we have in SAS area to deal with missing value .

Need output as :

CLOSED COMMIT PIPELINE   Total

100          50            10                160

.               60             70               130

50            .                100             150

I dont want to create another variable like if commit=. then 0 and use that . As in real scenario columns to add is much more that 3 and  have multiple combination.

Accepted Solutions
Solution
‎04-20-2016 12:26 AM
SAS Super FREQ
Posts: 831

## Re: How to calculate sum across a row in SAS VA having missing values ?

Hi

It is possible to create a calculated item using an IF expression, see the picture below:

Here is the text for the expression:

```(
IF ( 'CLOSED'n NotMissing )
RETURN 'CLOSED'n
ELSE 0 ) + (
IF ( 'COMMIT'n NotMissing )
RETURN 'COMMIT'n
ELSE 0 ) + (
IF ( 'PIPELINE'n NotMissing )
RETURN 'PIPELINE'n
ELSE 0 )```

Yes this could be easier, but it works.

Bruno

All Replies
SAS Super FREQ
Posts: 322

## Re: How to calculate sum across a row in SAS VA having missing values ?

Hello,

I don't think that what you want is possible through the Calculated Item dialog in the report builder. However, you should be able to create your Total column as part of your data query, in the Data Builder (Data Preparation) component of VA.

Sam

Occasional Contributor
Posts: 12

## Re: How to calculate sum across a row in SAS VA having missing values ?

Hi Sam,

Thanks.

But am using different parameter in VA designer(applies to different column) to get weighted value out of these . And these changes based on user choice .

Thus can't move the logic to Data builder or ETL.

Vivek

Solution
‎04-20-2016 12:26 AM
SAS Super FREQ
Posts: 831

## Re: How to calculate sum across a row in SAS VA having missing values ?

Hi

It is possible to create a calculated item using an IF expression, see the picture below:

Here is the text for the expression:

```(
IF ( 'CLOSED'n NotMissing )
RETURN 'CLOSED'n
ELSE 0 ) + (
IF ( 'COMMIT'n NotMissing )
RETURN 'COMMIT'n
ELSE 0 ) + (
IF ( 'PIPELINE'n NotMissing )
RETURN 'PIPELINE'n
ELSE 0 )```

Yes this could be easier, but it works.

Bruno

Occasional Contributor
Posts: 12