October 29 2012 Copyright 19992012 Leland Stanford Junior University All rights reserved Warning This presentation is protected by copyright law and international treaties Unauthorized reproduction of this presentation or any portion of it may result in severe civil and crimina ID: 717049
Download Presentation The PPT/PDF document "Merging with SQL HRP223 – 2012" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Merging with SQL
HRP223 – 2012
October 29, 2012
Copyright ©
1999-2012
Leland Stanford Junior University. All rights reserved.
Warning: This presentation is protected by copyright law and international treaties. Unauthorized reproduction of this presentation, or any portion of it, may result in severe civil and criminal penalties and will be prosecuted to maximum extent possible under the law.Slide2
CombiningWhen you have data in two tables, you need to tell SQL how the two tables are related to each other.Typically you have a subject ID number in both files. The variable that can be used to link information is called the key.Slide3
Demographics
Diagnosis
Here the two tables have different variables except the common ID.I want to know the diagnosis for this cohort.Slide4
Merging
Merging is trivially easy with EG. Choose a table and do the Query Builder…. And push the Join Tables button.Slide5
Double click on the dividing lines to make the columns wide enough to read.Slide6
Notice the name t1. In the SQL statements, variables from this table will have the prefix t1.
This table will be referred to as t2.
It noticed that the two tables have the common variable ID. Therefore it is going to match records that have a common value in ID.
Double click the link for details.Slide7
JoinsYou will typically do inner joins and left joins.Inner Joins: select the marching records
Left Joins: select all records on the left side and any records that match on the right.Slide8
Inner JoinsInner Joins are useful when you want to keep the information from the tables, if and only if, there are matches in both tables.
Here you keep the records where you have demographic and response to treatment information on people.Slide9Slide10
Left JoinsLeft joins are useful when you have a table with everybody on the left side of the join and not everyone has records in the right table.
A typical example has the left side with the IDs of everyone in a family and the right table has information on diagnoses. Not everyone is sick so you want to keep all the IDs on the left and add in diagnoses where you can.Slide11
Typical Left Join
Notice the numeric variable is formatted to display with words.Slide12Slide13
More CompleteSlide14
Coalesce The previous example leaves NULL for the people who are disease free. You probably want to list the rest as healthy.
The coalesce function returns the first non-missing value. Coalesce works on numeric lists.
CoalesceC works on character lists.Slide15
Expand the tree
Use the query builder with an advanced expression….Slide16Slide17Slide18
Complex CoalesceIf you are using left joins from multiple tables, coalesce can be really useful.Say you have people who have reported disease, other people have verified disease and the rest are assumed to be healthy. You can coalesce an indicator variable from the verified table and reported table and call everybody else healthy.Slide19
If the tables have indicator variables, once the tables
are linked, the coalesce function is easy:
COALESCEC(t3.status2 , t2.status1, "Healthy"))Slide20
No indicator variables?If the tables you are coalescing do not have indicator variables, just make them as part of the query by adding a column which has the ID in the child tables (e.g., reported and verified) recoded to a word like “reported” or “verified”.Slide21
The two new indicator columns.Slide22
Coalesce the new columnsOnce the new columns are created, create a new variable using the Advanced expression option for a new computed column. Then do coalesce on the new variables. Double click on the new variables and it will insert the code.Slide23
After double clicking the
ver
variable the code is inserted.
Don’t forget the comma before double clicking the rep variable.
After inserting reported and verified, put in another comma and the “healthy” option.Slide24