I am a fairly inexperienced Sas Viya Visual Analytics user and I am trying to work out how to select a specific subset of my data. I have a data table that includes Events, Event ID Numbers, Event Statuses, Tasks, Task ID Numbers, and Task Completion Statuses. An Event may have no or many Tasks, but each Task can only relate to one Event. So the relationship between tasks and events is a 'many to one relationship'. An Event can have one of 10 Event Statuses, which includes the status of 'Complete'. A Task can be in one of 3 Task Completion Statuses: 'Complete', 'Incomplete', or 'missing' (no value entered). What I wish to achieve is to select all Events that are not 'Complete' where none have an associated Task with a Task Completion Status of 'missing'. I am from a geographic information systems background, where I primarily used ArcGIS. This would be very easy to achieve in ArcGIS using relational databases. You would set up an 'Events' table that includes a unique identifier (Event ID Number) and includes all the event attributes. You would then create a 'Tasks' table using a unique identifier (Task ID Number), and all the task attributes. Included in the Task table is the 'Event ID Number' which identifies which event a task is related to. You then make a relationship between the two tables using 'Event ID Number'. Any tasks selected will then select the related events in the Events Table. So to achieve the selection I want, I would select all tasks in the tasks table that have a Task Completion Status of 'missing'. I then use this selection to select all the events in the Events Table that are related to the selected Tasks. I then invert the selection in the Events Table, so that I have now got a selection of events that have no tasks where the Task Completion Status is 'missing'. I now select from that selection all Events where the Event status is not 'Completed'. I now have all the event data selected that I am interested in. As SAS VA does not uses relational tables, and instead has all the data in one table, I have not been able to subset the data using the above method. I am guessing that I need to make a 'New Data Item', 'Calculated Item' (text not numerical), where I can apply a new label which has 2 states: 'Target' and 'Not Target'. I tried using a Boolean IF...ELSE Operator along with an AND Operator, which looked like this: IF ( ( 'Event Status'n NotIn ('Completion') ) AND ( 'Task Completion Status'n NotMissing ) ) RETURN 'Target' ELSE 'Not Target' This isolates all rows where Event status is not 'Completion' and where Task Completion Status is not 'missing, and labels them Target, and labels all other rows with Not Target, but I still end with Events that include Tasks that have a 'missing' Task Completion Status in my 'Target' group. Any advice would be greatly appreciated. Thanks, Tim
... View more