Add a Data Set Filter
A Data Set Filter is a mechanism to limit the subjects that are included in a data set or workspace.
Where is the filter's criteria defined?
The Filter field on the Attributes tab of the Filter page contains the criteria used to select subjects. An alias is used to identify the variables/medications used in the expression. All mathematical operators are valid including plus (+), minus (-), divide (/), and multiply (*), as well as other standard SQL Server date, mathematical and string. Connectors can also be used:"AND", "OR". Valid conditional statements include: equal (= ), not equal (!=), greaterthan (>), less than(<), greater than or equal to (>=), less than or equal to (<=), is not empty (IS NOT NULL). |
If you are going to use "not equal" (!=), you should include a statement for how to handle null values.
For example, if a value is null, the filter V1 != 1 will return false for the null value. Thus use V1 != 1 OR V1 IS NULL
How do I add variables to the filter?
From the Variable Finder tab, search for the variable / medication you want to add to the Filter. Then, select the variable / medication by clicking on the Add icon. This will assign an alias to the variable and add it to the variable alias list on the Attributes tab. If you select a variable that is collected on multiple occasions (Encounter variable), you will have to select the interval from which to apply the filter criterion. Use the alias when typing the conditional statement in Filter box on the Attributes tab.
Can you show me some filter examples.
The following examples assume the respective variable aliases.
- Sex (V1)
- Race (V2)
- City (V3)
- Date1 (V4)
- Date2 (V5)
Filter | Result Returned |
V1 = 0 | Subject that have a value of 0 for Sex. |
V1 IS NOT NULL | Subjects that have data for the variable Sex. |
V1 IS NULL | Subjects that do not have data for the variable Sex. |
V2 IN (1,4) | Subjects that have values of 1 or 4 for Race. |
V2 BETWEEN 2 AND 4 | Subjects that have values of 2, 3, or 4 for Race. |
V1 = 0 AND V2 = 0 | Subjects that have a value of 0 for Sex and 0 for Race. |
V1 = 0 AND (V2=0 OR V2=1) | Subjects that have a value of 0 for Sex and 0 or 1 for Race. |
LEN(V3) > 5 | Subjects whose value for City is greater than 5 characters. |
V3 LIKE 'GR%' | Subjects whose City begins with the characters "GR". |
SOUNDEX(V3) = SOUNDEX('GREEN') | Subject whose City sounds like "Green" (e.g., "Greene") |
DATEDIFF(day, V4, GETDATE()) <= 7 | Subjects with date V4 within the last 7 days |
DATEDIFF(month, V4, GETDATE()) = 1 | Subjects with date V4 last month |
DATEDIFF(day, V4, GETDATE()) < DATEDIFF(day, V5, GETDATE()) | Subjects with date V4 less than date V5 |
V4 BETWEEN '1/1/2012' AND '1/31/2012' | Subjects with a date V4 between 1/1/2012 and 1/31/2012 (the dates are inclusive) |
| V4 <= '7/31/2016' | Subjects with a date V4 prior to 7/31/2016 |
V3="O'''Fallon" | Subjects in the City name of O'Fallon |
DATEDIFF(yy,V1,GETDATE()) - case when GETDATE() < dateadd(yy,DATEDIFF(yy,V1,GETDATE()), V1) then 1 else 0 end BETWEEN 18 and 50 | Subjects with birth date of V1 and current age is between 18 and 50 inclusive |
DATEDIFF(yy,V1,GETDATE()) - case when GETDATE() < dateadd(yy,DATEDIFF(yy,V1,GETDATE()), V1) then 1 else 0 end >=18 | Subjects with birth date of V1 and current age is greater than or equal to 18 |
| MONTH(V1) = MONTH(GETDATE()) | Subjects with the month of date variable V1 as the current month |
Where can I learn more about filter criteria
SQL Tutorials: http://www.w3schools.com/sql/default.asp