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

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

vivekk17 — Tue, 19 Apr 2016 11:19:00 GMT

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.

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

Sam_SAS — Tue, 19 Apr 2016 17:33:06 GMT

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

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

BrunoMueller — Tue, 19 Apr 2016 19:55:53 GMT

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

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

vivekk17 — Wed, 20 Apr 2016 04:25:37 GMT

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

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

vivekk17 — Wed, 20 Apr 2016 04:26:46 GMT

Thanks Bruno