issue with if statment

[H_K]

Hi Everyone,

I have an issue with if statement, Which is adding extra one value extra on my calculation item, my case :

I have lists of students and it's categorize (failed ,success ,second exam..etc.).

total number of success students (540).

but when i Try to get this number(number of success student) using calculated item it got (541), Which is incorrect:

Check the attached file from sas visual anayltic.

,Any Idea?

Regards, 

[LinusH]

Please insert any code and sample data directly in the post, using the "SAS Submit" icon.

[Kurt_Bremser]

There is no code in the common sense. The Word document contains an edited screenshot from SAS VA. Let's hope that another point-and-click user can chime in here.

[H_K]

/**************************Calculated Item to Return Number of success students *****************************/

Distinct [_ByGroup_] ((
IF ( 'Academic_Student_Status'n In ('Pass', 'Pass_after_SecondExam') )
RETURN 'PC_ADM_PRS_ID'n[Formatted]
ELSE 0 ))

/*******************************************************/

This calculated Item return 541 of students which is wrong, The actual value 540

[HunterT_SAS]

I think maybe the calculation is a bit flawed here. I assume there are instances where Academic_Student_Status'n = 'Fail' so in addition to PC_ADM_PRS_ID values, you are getting 0 returned for that calculation, and that is getting included in the Distinct count. In other words you are getting a 540 values for PC_ADM_PRS_ID, and then 0, for a total of 541 distinct values.

If you are just using this in a single Key Value object, what I think you should probably do instead is just create a single calculated item for the Distinct Count of PC_ADM_PRS_ID:

Distinct [_ByGroup_] ('PC_ADM_PRS_ID'n[Formatted])

Add that to the Key Value object, and then add a local filter on the Key Value object for Academic_Student_Status'n In ('Pass','Pass_after_SecondExam'). Meaning - click the Key Value object, click the Filters pane on the right hand side, add this filter. 

Otherwise if you already doing some kind of filtering that I'm not seeing - another possibility is if there happens to be a missing value somewhere in the PC_ADM_PRS_ID data item, then Distinct by default includes this in the count. 

If that's the case, you might you might be able to account for this in your calculation by adding a second IF statement to check for missing. Something like this (note this is not exact, just freehand typing into this to illustrate the point):
Distinct [_ByGroup_] ((
IF ( 'Academic_Student_Status'n In ('Pass', 'Pass_after_SecondExam') )
RETURN (IF ('PC_ADM_PRS_ID'n[Formatted] NotMissing) 
                    RETURN 'PC_ADM_PRS_ID'n[Formatted]
                    ELSE 0))
ELSE 0 ))

Not sure which VA version you have but there is a property to control this that you or your Administrator can set, and here are the steps for Viya 3.5:
1. Log in to SAS Environment Manager as an Administrator

2. Go to Configuration > All Services > Report Data service

3. Edit the "sas.reportdata.properties" section, and find the property "ignoreMissingValuesInCountDistinct". 

4. Turn this one on, save changes, and restart the Report Data service. 

For Viya 4 the steps are very similar. For SAS 9.4 this property is in SAS Management Console in the Application Management > SAS Application Infrastructure > Visual Analytics 7.5 properties, and requires a restart of SASServer12_1.