SAS® SQL programming tricks for handling longitudinal datasets

SAS® SQL programming tricks for handling longitudinal datasets

SAS SQL programming tricks for handling longitudinal datasets Hua (Josh) Weng Washington University School of Medicine Advantages of SAS SQL Join tables without sorting, on different matching names Match multiple tables at different levels Summarize data while doing queries SAS user SAS user Other coder Has similar syntax to other SQL. SAS SQL aggregate/summary functions AVG|MEAN arithmetic mean or average of values COUNT|FREQ|N number of nonmissing values CSS corrected sum of squares CV coefficient of variation (percent) MAX largest value MIN smallest value NMISS number of missing values PRT is the two-tailed p-value for Student's t statistic, T with degrees of freedom. RANGE range of values STD standard deviation STDERR standard error of the mean SUM sum of values SUMWGT sum of the WEIGHT variable values T Student's t value for testing the hypothesis that the population mean is zero USS uncorrected sum of squares VAR variance Column-wise vs. Row-wise calculation ID Time (yr.) 1 2 3 101 101 2012 2012 1 1 2 2

3 3 101 101 2013 2013 2 2 3 3 4 4 101 101 2014 2014 3 3 4 4 5 5 102 102 2012 2012 4 4 5 5 6 6 102 102 2013 2013 5 5 6 6 7 7 102 102 2014 2014 6 6 7 7 8 8 Proc SQL; mean(1) as from table0;

DATA table1; SET table0; = mean (of 1- 3); RUN; Proc SQL; mean(1) as from table0 ID; Example 1. To extract data for a particular visit ID Time Time (yr.) (yr.) 1 1 101 101 101 101 101 101 101 102 102 102 102 102 102 2012 2012 2013 2013 2014 2014 2014 2013 2012 2013 2012 2013 2014 2014 2013 2014 2015 2015 2014 1 1 2 2 3 3 3 4 4 5 5 6 6 TASK: To isolate the data from the 2nd visit. 1

1 2 2 3 3 1 1 2 2 3 3 Example 1 (cont.) Through DATA step: combing built-in keyword FIRST. and a counter statement: data example1; set example; Vnum + 1; by ID; if first.ID then Vnum = 1; run; The third line, Vnum + 1, creates the variable Vnum and adds one to each observation as SAS processes the data step. There is an implicit retain statement in this statement. Through SQL: combing MONOTONIC() and an aggregate function MIN(): proc sql noprint; create table example1 as select *, MONOTONIC() MIN(MONOTONIC()) + 1 as Vnum from example group by id; ID MONOTO NIC() MIN() Vnum 101 1 1 1 101 2 1 2 101 3 1 3 102 4 4 1 102

5 4 2 102 6 4 3 Example 2. To join tables with unmatched TIME ID date_x 101 101 102 102 102 102 11MAR05 12MAY08 15NOV07 15NOV07 27JAN11 27JAN11 TASK: To join the two tables, chose y value with the nearest date. 11 22 44 44 55 55 ID date_x date_x 101 101 101 101 102 102 102 102 11MAR05 11MAR05 12MAY08 12MAY08 15NOV07 15NOV07 27JAN11 27JAN11

11 11 22 22 44 44 55 55 ID date_y 101 101 101 101 102 102 102 102 102 102 date_y date_y 14FEB05 27MAR07 09MAR09 09MAR09 14NOV06 14NOV06 27AUG08 27AUG08 05DEC10 05DEC10 14FEB05 14FEB05 09MAR09 09MAR09 27AUG08 27AUG08 05DEC10 05DEC10 1 1 3 3 5 5 6 6 1 2 3 3 4 4 5 5 6 6 Example 2. (cont.) proc sql noprint; create table x_y as select x.*, y.*

from x left join y on x.id = y.id GROUP BY X.ID, DATE_X HAVING ABS(DATE_X - DATE_Y) = MIN(ABS(DATE_X - DATE_Y)); abs(date MIN(abs( _x date_x date_x ID date_y ID date_y) date_y)) 101 11MAR05 11 101 14FEB05 1 25 25 101 11MAR05 11 101 27MAR07 2 746 25 101 11MAR05 11 101 09MAR09 3 1459 25 101 12MAY08 22 101 14FEB05 1 1183 301 101 12MAY08

22 101 27MAR07 2 412 301 101 12MAY08 22 101 09MAR09 3 301 301 102 102 Example 3. To impute missing values with values having closest TIME ID ID Time Time 101 101 101 101 101 101 101 102 101 102 102 102 102 102 102 102 102 01Nov2001 01Nov2001 02Nov2002 30Oct2003 02Nov2002 03Nov2004 30Oct2003 04Nov2005 03Nov2004 01Dec2010 04Nov2005

11Nov2011 01Dec2010 03Dec2012 11Nov2011 15Nov2013 03Dec2012 08Nov2014 15Nov2013 102 08Nov2014 (LOCF*) (NNI**) 555 555 . . 666 . . 666 888 . . 888 . .. . 999 . 555 555 555 666 555 666 666 888 666 888 888 888 888 888 888 999 888 555 555 666 555 666 666 666 666 888 666 888 888 999 888 999 999 999 999 999 999 999 *LOCF: Last Observation Carried Forward; **NNI: Nearest Neighbor Imputation Example 3. (cont.)

proc sql noprint; create table X_imputed as select a.*, b.x as x_new, b.visit_date as imputation_date from X_original as a, X_original (where = (x ne .)) as b where a.id = b.id group by id, visit_date having abs(a.visit_date abs(a.visit_da b.visit_date) = MIN(abs(a.visit_dateImputation_d - b.visit_date)) ID visit_date te ate ; b.visit_date) 101 01Nov2001 . 555 01Nov2001 0 101 01Nov2001 . 666 03Nov2004 1098 101 02Nov2002 . 555 01Nov2001 366 101 02Nov2002 . 666 03Nov2004 732 101 30Oct2003 . 555 01Nov2001 728 101 30Oct2003 .

666 03Nov2004 370 101 03Nov2004 . 555 01Nov2001 1098 101 03Nov2004 . 666 03Nov2004 0 101 04Nov2005 . 555 01Nov2001 1464 101 04Nov2005 . 666 03Nov2004 366 MIN(abs(a.visi t_date b.visit_date)) 0 0 366 366 370 370 0 0 366 366 Summary Proc SQL; MIN/MAX/NMISS/ ID ; ID, DATE SQL Self-join (single imputation)

Recently Viewed Presentations

  • The Holy Spirit of God - O'Neal Church of Christ

    The Holy Spirit of God - O'Neal Church of Christ

    not ..the Holy Spirit. * Only a person can be Grieved!. John . 8:32 - And ye shall know the truth, and the truth shall make you free. John 3:5 - Jesus answered, Verily, verily, I say unto thee, Except...
  • Poetry Forms

    Poetry Forms

    living, relaxing, singing. harmony, free. Peace. Giving Poem. Giving poetry names a subject that gives an interesting or unusual gift. ... The Challenger went up into space. On board were astronauts and a teacher. Trying to find an unexplored place.
  • Spelling, Punctuation And Grammar English Curriculum 2014 Changes

    Spelling, Punctuation And Grammar English Curriculum 2014 Changes

    Year 6: Detail of content to be introduced (statutory requirement) Word. The difference between vocabulary typical of informal speech and vocabulary appropriate for formal speech and writing [for example, find out - discover; ask for - request; go in -...
  • Amateurfunk über Satelliten

    Amateurfunk über Satelliten

    DM 1800,- Transverter 13/23cm DM 1900,- 10GHz Konverter DM 600,- Antennen DM 700,- Computer vorh.+ DM 200,- Software Internet „Kleinteile" DM 500,- Zusammenfassung - Kosten / Summe Variante 1 DM 3300,- RX 13 cm TX 23 cm 1 Tranceiver Variante...
  • Government and National Governing Body Initiatives.

    Government and National Governing Body Initiatives.

    Government and National Governing Body Initiatives. Contemporary Sporting Issues Socio-cultural Issues Mr. P. Leighton Today's Session… Hand in "Fact-Sheet" for pack to be distributed tomorrow. Look at the different Government initiatives to involve people in sport such as… The National...
  • TROOP LEADING PROCEDURES 327th Signal BN (ABN) REFERENCES

    TROOP LEADING PROCEDURES 327th Signal BN (ABN) REFERENCES

    ISSUE THE WARNING ORDER Provide a brief order outlining upcoming events to prepare subordinates for movement. This order provides initial instructions to allow preparation to begin as soon as possible (i.e. draw ammunition, rations, water, supplies. etc.). The warning order...
  • Transforming the Health Care Response to Family Violence

    Transforming the Health Care Response to Family Violence

    Recognizing that victims of domestic violence often seek medical care before calling the police, and that many physicians lack adequate training and guidelines to address domestic violence, AB 890 was introduced to the California legislature in 1993 and passed into...
  • Status Report on the NHLBI-Sponsored CVD Prevention Guidelines

    Status Report on the NHLBI-Sponsored CVD Prevention Guidelines

    Evidence-Based Clinical Practice Guidelines for CVD Prevention Outcomes Required to Include a Randomized Controlled Trial Overall mortality, CVD-related mortality, CKD-related mortality, myocardial infarction, heart failure, hospitalization for heart failure, stroke