Filtering using Character Parameters

[Bubson]

I am a fairly new user of SAS VA and have a data set that I wish to filter that I am having difficulty getting the desired result.

Lets say we have a dataset of houses, each Identified by 'House Number' (a unique Identifier ).

Each house consists of rooms of a specific type (Study, Masterbed Room1, Bedroom1, Lounge, Bathroom1 ...), which we will call 'Room Type'.

Each house consists of multiple rooms which may or may not have all 'Room Types'. Each room has a unique identifier called 'Room Number'.

My aim is to filter all 'House Number's so that I subset houses that include at least one 'Study' and 2 Bathrooms (so has 'Bathroom2' in 'Room Type'), and then display via a list table all the room types of that subset of houses.

I thought I could achieve this by creating a calculated value, where

House Subset =

IF ( 'Room Type'n In ('Study',

'Bathroom2') )

RETURN 'House Number'n

ELSE ' '

I am not proficent at building Boolean arguments in calculated values, and do not have experience with Python. Please keep any response in simple terms that a newbie can understand.

When I create a Crosstab using House Subset, and include 'Room Type' and as a row in the Crosstab, I only see room types of Bathroom2 and Study. THis is not the result I want. I want to see all room types of the houses that include Bathroom2 and Study.

So I abandon using a claculated value, and attempt to get the result I want by filtering.

If I filter a Crosstab by Roomtype where selection = Bathroom2 and Study, this obviously filters out other room types, which is not the result I want.

I am not very familiar with using Parameters, but maybe I can use a Parameter in a filter like this:

IF ( 'Room Type Parameter'p In (Bathroom2, Study) )
RETURN 'House Number'n
ELSE ''

But the Filter Messages indicate "Type Mismatch: Expected typle at this location is Boolean, type of item being applied is Character"

Please advise how I can subset the dataset to include only those houses that include Bathroom2 and Study, but also display all other roomtypes of that subset.

If there is a tutorial or example similar to this that I can follow through, that would be excellent. I am not proficent with script and used Visual option to construct filters and create calculated values.

Any help greatly appreciated.

[Sam_SAS]

Hello,

For questions like this that involve data, it is super helpful to provide a mockup or a redacted sample of what your data looks like.

If I understand correctly, you have data like this:

House Number	Room Type	Room Number
1	Bedroom	1-1
1	Bathroom	1-2
1	Study	1-3
2	Bedroom	2-1
2	Bathroom	2-2

That is, each house is represented by multiple rows, and you want to select all of the rows for a given house based on what types of room it has. Is that right?

I'm not sure whether this is possible to do easily in VA. I was going to propose restructuring like this:

House Number	Bedrooms	Bathrooms	Studies	Lounges
1	2	1	1	0
2	1	1	0	0

This would be much easier to work with, but this only works if you can drop your Room Number column.

Probably the thing you should do is add a new column as part of your data preparation. That might look like this:

House Number	Has Study	Has 2 Baths	Room Type	Room Number
1	1	1	Bedroom	1-1
1	1	1	Bathroom	1-2
1	1	1	Bathroom	1-3
1	1	1	Study	1-4
2	0	0	Bedroom	2-1
2	0	0	Bathroom	2-2

Does that make sense? With this structure you could filter on "Has Study=1 AND Has 2 Baths=1"

[Bubson · Accepted solution]

I used the idea found as the solution here: 

https://communities.sas.com/t5/SAS-Visual-Analytics/Working-with-Calculated-Items/m-p/859094#M16974 

Make an aggregated data source of the subset of houses that match the desired criteria using only the unique identifier for house. Then join to the original houses table. Make sure the join is based on unique identifier of houses only. Then filter out missing values (that is all unique identifiers that do not exist in the houses subset). My dataset is actually not based on houses and rooms, that was an analogy. The data is restricted, otherwise I would show you a screenshot of the end result.