HMIS, R, and SQL -- Basics

Reading time ~3 minutes

Hacker Introduction

I’m a hacker.  If you find errors, please leave comments below.  If you have an opinion I’ll hear it, but I’m often not likely to agree without some argument.

Joins (Merging Data)

Probably the best part of R and SQL is their ability to quickly combine data around a key.  For example, in HMIS CSVs the Client.csv contains a lot of demographic information and the Enrollment.csv contains a lot of assessment information.  This makes it difficult when needing a count of the total participants who are veterans and disabled, since the veteran information is in Client.csv and disability information is in the Enrollment.csv.  However, both R and SQL contain the join functions.  

Joins are a hughely expansive topic; I’m not going to try to cover all their quirks, but here’s some videos I found helpful:

The two useful joins for HMIS data are LEFT JOIN and INNER JOIN.  The left join keeps all the data in the left table and data matching from the right table and the inner join keeps only data which matches.

Here’s an example in the context of the Client.csv and Enrollment.csv:


PersonalID FirstName VeteranStatus
12345 Jane Yes
54321 Joe No


PersonalID FirstName DisablingCondition
12345 Jane Yes
54321 Joe No
45321 Sven Yes

Here are the two join statements and their results for the data above

   FROM client a 
   LEFT JOIN enrollment b ON a.Personal=b.PersonalID

This join should result in the following:

PersonalID FirstName VeteranStatus DisablingCondition
12345 Jane Yes Yes
54321 Joe No No
45321 Sven NULL Yes

Notice Sven was kept, even though he had no entry the Client.csv.  After the join, since he had no 
And the inner join would look like this:

       FROM client a 
       INNER JOIN enrollment b ON a.Personal=b.PersonalID

This join should result in the following:

PersonalID FirstName VeteranStatus DisablingCondition
12345 Jane Yes Yes
54321 Joe No No


PersonalID <- sqldf("SELECT DISTINCT PersonalID FROM client")

Method above creates a vector of all the PersonalIDs in the client data-frame, which came from the Client.csv.  The DISTINCT command takes only one ID if there are more than two which are identical.  In short, it create a de-duplicaed list of participants.

For example,

PersonalID  OtherData
12345 xxxxxxxxx
56839 xxxxxxxxx
12345 xxxxxxxxx
32453 xxxxxxxxx

Should result in the following,


This is useful in creating a key vector, given other CSVs have a one-to-many relationship for the PersonalID.  For example,

The Enrollment.csv looks something like this

PersonalID ProjectEntryID  EntryDate
12345 34523 2016-12-01
56839 24523 2015-09-23
12345 23443 2014-01-10
32453 32454 2015-12-30

This reflects a client (i.e., 12345) entering a project twice, once on 2014-01-10 and the other 2016-12-01.

Count of Total Participants:

SELECT COUNT(PersonalID) as 'Total Participants' FROM client

This query should give a on row output, counting the number of clients in the data-frame.

Total Participants
1 1609

However, if there are duplicate PersonalIDs it’ll count each entry as an ID.  To get a count of unique clients in a data-frame add the DISTINCT command.

SELECT COUNT(DISTINCT(PersonalID)) as 'Unique Total Participants' FROM client

Conditional Data

Often in HMIS data it is necessary to find a collection of participants which meet a specific requirement.  For example, “How many people in this data-set are disabled?”  This is where the WHERE statement helps a lot.

SELECT PersonlID FROM clientAndEnrollment WHERE disability = 'Yes'

This statement will return a vector of all the PersonalID’s of participants who stated they were disabled.  The total participant query could be used, but there is an alternative method.

               disability = 'Yes' THEN 1 ELSE 0 
           END) as DisabledCount

The above statement uses the CASE WHEN END statement, which I understand as SQL’s version of the IF statement.  Here’s C equivalent:

for(int i = 0; i < total_participants; i++)
    if(disability == true){


Boolean operaters can be used to get more complex conditional data:

SELECT PersonalID FROM clientAndEnrollment 
       WHERE disability = 'Yes' 
       AND gender = 'Female'

This statement will provide a vector of all the PersonalID’s for clients who are disabled and female.

Ok, good stopping point for now.

SQL CASE and R Paste

## SQL CaseThe SQL `CASE` function is one of my favorite. The command basically works like if-then command. If you are familiar with if...… Continue reading

HMIS, R, SQL -- Work Challenge Four

Published on August 08, 2017

C3 HMIS Graph Gallery

Published on August 07, 2017