Filter Data Item using other different data items types

[Bubson]

Here is a hypothetical problem that is related to problem that I am trying to solve.

If we have a Data Item, lets called it ‘House Number’, which is a house with a unique identifier number.

Each house has rooms with a unique identifier, which we will call ‘Room Number’.

Each room is  a ‘Room Type’, of which there are multiple types.

Let’s say that I wish to filter ‘House Number’ by all ‘Room Type’ that equal ‘Study’.

So I only want to filter out all ‘House Number’ data items that do not include ‘Study’ in ‘Room Type’.

I then wish to create a list of these houses, where I list and count the number of other ‘Room Types’ each house has in this subset houses.

I tried the following approach:

1). Create a calculated data item of ‘House Number’ as follows:

‘House Subset’ =

IF ( 'ROOM_TYPE'n In ('Study') )

RETURN 'House Number'n

ELSE ' '

2) Create a List Table, using the following order:

 ‘House Number’,’Room Type’, ‘Distinct Count of Room Number’

Without any filter applied I see all houses in the database, with a list of room types per house, and a count of the room types per house.

I thought if I use ‘House Subset’ as a filter on the list table, this will remove all ‘House Number’ items that do not include ‘Study’ in ‘Room Type’, leaving only those that do include ‘Study’. But I want to see all ‘Room Types’ per house, not just studies, and a distinct count of ‘Room Number’ per Room Type’ per house.

When I apply a filter to the List Table using ‘House Subset’, I get zero rows of data.

If I create another List Table using ‘House Subset’, ’Room Type’, ‘Distinct Count of Room Number’, all I see is a count of rooms that are of the type ‘Study’.

My calculated data item ‘House Subset’ appears to be disconnected from other types of ’Room Type’.

Should I be using a different approach to get a list that will show count all rooms in houses that include studies?

Obviously I should. Any suggestions?

[acordes]

There are many ways to solve this. 

I propose a parameter tied to a list control object which is then used in the list table's filter with an 'in' operator. 

Sample data generation based on https://blogs.sas.com/content/iml/2011/07/13/simulate-categorical-data-in-sas.html 

%let NSim = 100;
data Sample; 
drop i; 
array prob1[4] _temporary_ (0.3, 0.2, 0.1, 0.4); 
array house (4) $20. ('river' 'mountain' 'city' 'ocean' );
array prob[5] _temporary_ (0.2, 0.1, 0.2, 0.1, 0.4); 
array type (5) $20. ('study' 'guests' 'kitchen' 'restroom' 'dormitory');
call streaminit(54321); 
do i = 1 to &NSim;
   houseNumber = house( rand("Table", of prob1[*]));
   RoomType = type (rand("Table", of prob[*]));
   output; 
end; 
run;

proc sort data=sample;
by housenumber;
run;

data casuser.sample1(promote=yes);
set sample;
by housenumber ;
if first.housenumber then roomnumber=0;
roomnumber+1;
if not ((housenumber='river' and roomtype='study') or (housenumber='city' and roomtype='guests'));
keep roomnumber housenumber roomtype;
run;

( 'RoomType'n In 'RoomType Parameter'p )

[Bubson]

Thank you for going to the trouble to create the report from simulated data. Unfortunately, I have not yet learn to use a scripting language in SAS VA, so I'm only familar with menu driven use.

I'll have a go at using a parameter using a list control object, and let you know if it works for me.

I have not used parameters before, so will be something new to learn. Thank you for giving your time to answer my question 🙂

[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). End result looks much like your table above.