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

[vivekk17]

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.

[Sam_SAS]

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

[vivekk17]

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

[BrunoMueller · Accepted solution]

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

[vivekk17]

Thanks Bruno