122S:166Computing in StatisticsMore on mergingLect 17Nov. 1, 2006Kate Cowles374 SH, [email protected] at multiple records for eachsite• Suppose we want to look at the annual sul-fate ion deposition at the CO sites for eachyear from 1991-2000, inclusive• We want to estimate site-specific random slopeson year, as well as fixed-effects intercept andcoefficients of year and elevation• Which SAS procedure?• How should input data look?3• we need a “match merge”• must process records in both files by a com-mon variable• then merge them by this variable4options linesize = 75 pagesize = 60 nodate nonumber ;data depo ;infile ’depoRep90s.asp’ firstobs = 8 ;input SiteID $ Per $8. Year Crit1 Crit2 Crit3 Crit4 Ca MgK Na NH4 NO3 InorgN Cl SO4 HLab HField Svol Ppt PctValidF ValidL Days @196 Date1 mmddyy10. @209 Date2 mmddyy10. ;drop Per Crit1-Crit4 Ca Mg K Na NH4 NO3 InorgN Cl HLabHField Svol Ppt Pct ValidF ValidL ;daysop = Date2 - Date1 ;format Date2 Date1 date8. ;run ;*proc sort ; * needed if records are not already in order ;*by SiteID ; * by SiteID ;*run ;data depo ;set depo ;by SiteID ;run ;proc print data = depo (obs=25) ;run ;data sites ;infile ’/space/kcowles/166/lectures/lect1mkc/stateCO.asp’ firstobs = 19missover ;input @13 SiteID $ @20 sitename $18. @40 strtdate mmddyy10.@53 stopdate mmddyy10. @68 elev ;if strtdate ne . ; * subsetting if: exclude obs meeting condition ;format strtdate stopdate date8. ;drop sitename ;5run ;* proc sort ;* by SiteID ;* run ;data sites ;set sites ;by SiteID ;run ;* proc print ;* run ;data combined ;merge depo sites ;by SiteID ;run ;proc print data=combined ;run ;6The log file1 options linesize = 75 pagesize = 60 nodate nonumber ;23 data depo ;4 infile ’depoRep90s.asp’ firstobs = 8 ;5 input SiteID $ Per $8. Year Crit1 Crit2 Crit3 Crit4 Ca Mg6 K Na NH4 NO3 InorgN Cl SO4 HLab HField Svol Ppt Pct6 ! ValidF ValidL7 Days @196 Date1 mmddyy10. @209 Date2 mmddyy10. ;8 drop Per Crit1-Crit4 Ca Mg K Na NH4 NO3 InorgN Cl HLab8 ! HField9 Svol Ppt Pct ValidF ValidL ;10 daysop = Date2 - Date1 ;11 format Date2 Date1 date8. ;12 run ;NOTE: The infile ’depoRep90s.asp’ is:FileName=/tmp_mnt/space/kcowles/166/lectures/lect2mkc/depoRep90s.asp,Owner Name=kcowles,Group Name=faculty,Access Permission=rw-------,File Size (bytes)=35962NOTE: 161 records were read from the infile ’depoRep90s.asp’.The minimum record length was 218.The maximum record length was 218.NOTE: The data set WORK.DEPO has 161 observations and 7 variables.NOTE: DATA statement used:real time 0.12 secondscpu time 0.07 seconds71314 *proc sort ; * this is needed if records are not already in14 ! order ;15 *by SiteID ; * by SiteID ;16 *run ;1718 data depo ;19 set depo ;20 by SiteID ;21 run ;NOTE: There were 161 observations read from the dataset WORK.DEPO.NOTE: The data set WORK.DEPO has 161 observations and 7 variables.NOTE: DATA statement used:real time 0.01 secondscpu time 0.02 secondsSkipping stuff about sites file as we have seen it all.48 data combined ;49 merge depo sites ;50 by SiteID ;51 run ;NOTE: There were 161 observations read from the dataset WORK.DEPO.NOTE: There were 18 observations read from the dataset WORK.SITES.NOTE: The data set WORK.COMBINED has 162 observations and 10 variables.NOTE: DATA statement used:real time 0.02 secondscpu time 0.02 seconds8The SAS SystemSiteObs ID Year SO4 Days Date1 Date2 daysop1 CO00 1991 2.08 364 01JAN91 31DEC91 3642 CO00 1992 1.20 365 31DEC91 30DEC92 3653 CO00 1993 1.50 370 30DEC92 04JAN94 3704 CO00 1994 1.31 364 04JAN94 03JAN95 3645 CO00 1995 1.46 364 03JAN95 02JAN96 3646 CO00 1996 1.07 364 02JAN96 31DEC96 3647 CO00 1997 1.10 364 31DEC96 30DEC97 3648 CO00 1998 1.28 364 30DEC97 29DEC98 3649 CO00 1999 1.01 364 29DEC98 28DEC99 36410 CO00 2000 1.18 367 28DEC99 05DEC00 34311 CO01 1991 3.19 363 02JAN91 31DEC91 36312 CO01 1992 3.09 364 31DEC91 29DEC92 36413 CO01 1993 2.30 371 29DEC92 04JAN94 37114 CO01 1994 2.98 364 04JAN94 03JAN95 36415 CO01 1995 3.64 365 03JAN95 03JAN96 36516 CO01 1996 2.99 363 03JAN96 31DEC96 36317 CO01 1997 2.53 364 31DEC96 30DEC97 36418 CO01 1998 2.44 364 30DEC97 29DEC98 36419 CO01 1999 3.49 364 29DEC98 28DEC99 36420 CO01 2000 2.06 371 28DEC99 02JAN01 37121 CO02 1991 9.88 365 31DEC90 31DEC91 36522 CO02 1992 9.46 364 31DEC91 29DEC92 36423 CO02 1993 11.10 371 29DEC92 04JAN94 37124 CO02 1994 7.96 364 04JAN94 03JAN95 36425 CO02 1995 12.87 364 03JAN95 02JAN96 3649The combined fileSiteObs ID Year SO4 Days Date1 Date2 daysop strtdate stopdate elev1 CO00 1991 2.08 364 01JAN91 31DEC91 364 22APR80 . 22982 CO00 1992 1.20 365 31DEC91 30DEC92 365 22APR80 . 22983 CO00 1993 1.50 370 30DEC92 04JAN94 370 22APR80 . 22984 CO00 1994 1.31 364 04JAN94 03JAN95 364 22APR80 . 22985 CO00 1995 1.46 364 03JAN95 02JAN96 364 22APR80 . 22986 CO00 1996 1.07 364 02JAN96 31DEC96 364 22APR80 . 22987 CO00 1997 1.10 364 31DEC96 30DEC97 364 22APR80 . 22988 CO00 1998 1.28 364 30DEC97 29DEC98 364 22APR80 . 22989 CO00 1999 1.01 364 29DEC98 28DEC99 364 22APR80 . 229810 CO00 2000 1.18 367 28DEC99 05DEC00 343 22APR80 . 229811 CO01 1991 3.19 363 02JAN91 31DEC91 363 04OCT83 . 121312 CO01 1992 3.09 364 31DEC91 29DEC92 364 04OCT83 . 121313 CO01 1993 2.30 371 29DEC92 04JAN94 371 04OCT83 . 121314 CO01 1994 2.98 364 04JAN94 03JAN95 364 04OCT83 . 121315 CO01 1995 3.64 365 03JAN95 03JAN96 365 04OCT83 . 121316 CO01 1996 2.99 363 03JAN96 31DEC96 363 04OCT83 . 121317 CO01 1997 2.53 364 31DEC96 30DEC97 364 04OCT83 . 121318 CO01 1998 2.44 364 30DEC97 29DEC98 364 04OCT83 . 121319 CO01 1999 3.49 364 29DEC98 28DEC99 364 04OCT83 . 121320 CO01 2000 2.06 371 28DEC99 02JAN01 371 04OCT83 . 1213...118 CO94 1997 3.66 364 31DEC96 30DEC97 364 04NOV86 . 2524119 CO94 1998 4.34 364 30DEC97 29DEC98 364 04NOV86 . 2524120 CO94 1999 3.61 364 29DEC98 28DEC99 364 04NOV86 . 2524121 CO94 2000 2.79 371 28DEC99 02JAN01 371 04NOV86 . 2524122 CO95 . . . . . . 29JUL86 02JAN90 2758123 CO96 1991 4.37 364 01JAN91 31DEC91 364 29JUL86 . 324910Omitting records missing from one filedata combined ;merge depo(in=ina) sites ;* creates variable "ina" -- true if record is in depo, o.w. false ;by SiteID ;if ina ; * subsetting if ;run ;11Proc mixedproc mixed data = combined ;class SiteID ;model so4 = year elev / s ;random year / subject = SiteID s ;run ;12Proc transpose: exchanging rows andcolunns• Suppose instead we needed to process thedata in the following format:– a single row (record) for each site– a column (variable) for each year’s so4 valueproc transpose data=combined
View Full Document
Unlocking...