Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

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: 676

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:

Capture.PNG

 

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

View solution in original post


All Replies
SAS Super FREQ
Posts: 286

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: 676

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:

Capture.PNG

 

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

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

Thanks Bruno
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 673 views
  • 0 likes
  • 3 in conversation