Adding Age at Enrollment - greenriver/hmis-warehouse GitHub Wiki

Description

This workflow documents the process for adding the Age at Time of Enrollment to the HMIS csvs. It can also be used to deidentify client details, by removing client DOB when sharing information in preparation for transferring data to a third party.

Workflow

  1. Navigate to the HUD HMIS CSV Export and queue the report by the desired parameters. If you are planning to transfer this data to a third party, it probably makes sense to hash the data to reduce the risk of transferring SSNs and names to an outside entity.

  2. Download the report after it is finished running.

  3. Create Lookup File for Age at Enrollment

    • Create a new Excel document with two tabs and label them Enrollment and Client.

    • Open the Enrollment.csv and Client.csv files in Excel.

    • Copy EnrollmentID, PersonalID, ProjectID, and EntryDate columns from the Enrollment.csv into columns A, B, C, D, respectively, on the Enrollment Tab.

    • Copy PersonalID and DOB columns from the Client.csv into columns A and B, respectively, on the Client Tab.

    • In the Enrollment tab add two new columns, on E and D, and name them DOB and Age.

      • Under DOB add this calculation to the whole column: =VLOOKUP(B2,Client!A:B,2,TRUE). Change the cell format to Date if applicable.

      • Under Age add this calculation to the whole column: =ROUND((D2-E2)/365.25,0)

    • Copy the Enrollment ID, PersonalID, EntryDate, and Age columns and paste special as Values into a new Excel file and save it as a CSV.

  4. If applicable, remove any other identifiable data from the csv files.