topic Re: How do I combine two data sets (Primary Key has multiple unique observations) in SAS Studio https://communities.sas.com/t5/SAS-Studio/How-do-I-combine-two-data-sets-Primary-Key-has-multiple-unique/m-p/596435#M8145 <P>There are many ways how you can combine data. Below uses a data step MERGE.</P> <PRE><CODE class=" language-sas">proc sort data = schoolsurvey; by family_ID Student_ID; Run; data SixthGrade; Set schoolsurvey; by family_ID Student_ID; if not missing(School); rename DOB = SixthGDOB Grade=Grade6 School=School6 Student_ID=Student_ID6; run; /* check for duplicates to avoid m:m relationships when merging data later on */ proc sort data=SixthGrade nodupkey out=SixthGrade dupout=SixthGrade_Duplicates; by family_ID; run; title 'duplicates'; proc print data=SixthGrade_Duplicates; run; title; data combined; merge schoolsurvey SixthGrade; by family_ID; run; title 'combined data'; proc print; run; title;</CODE></PRE> Tue, 15 Oct 2019 05:50:32 GMT Patrick 2019-10-15T05:50:32Z How do I combine two data sets (Primary Key has multiple unique observations) https://communities.sas.com/t5/SAS-Studio/How-do-I-combine-two-data-sets-Primary-Key-has-multiple-unique/m-p/596430#M8144 <P>I am being asked to "Combine the data set from part b) with the original data, and create a variable that is the age difference in years between the sixth-grader and the sibling."</P><P>&nbsp;</P><P>I've examined the original data set and the results are:&nbsp;</P><PRE><CODE class=" language-sas">The CONTENTS Procedure Data Set Name WORK.SCHOOLSURVEY Observations 778 Member Type DATA Variables 5 Engine V9 Indexes 0 Created 10/15/2019 00:41:31 Observation Length 32 Last Modified 10/15/2019 00:41:31 Deleted Observations 0 Protection Compressed NO Data Set Type Sorted NO Label Data Representation SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64 Encoding utf-8 Unicode (UTF-8) Engine/Host Dependent Information Data Set Page Size 65536 Number of Data Set Pages 1 First Data Page 1 Max Obs per Page 2038 Obs in First Data Page 778 Number of Data Set Repairs 0 Filename /tmp/SAS_work0F1B00003D3D_localhost.localdomain/SAS_workB07300003D3D_localhost.localdomain/schoolsurvey.sas7bdat Release Created 9.0401M6 Host Created Linux Inode Number 671960 Access Permission rw-rw-r-- Owner Name sasdemo File Size 128KB File Size (bytes) 131072 Alphabetic List of Variables and Attributes # Variable Type Len Format Label 5 DOB Num 8 MMDDYY10. Date of Birth 1 Family_id Num 8 Family ID 4 Grade Char 4 Grade Level 3 School Char 2 School (RC=Rachel Carson, GV=Green Valley, RG=Redwood Grove) 2 Student_ID Num 8 Student ID</CODE></PRE><P>Here is the data:&nbsp;</P><P>&nbsp;</P><PRE><CODE class=" language-sas">Original Data ObsFamily_id Student_ID School Grade DOB 1 90021 103699 4th 07/24/2004 2 90021 127945 RG 6th 01/26/2002 3 90021 149229 2nd 10/28/2005 4 90053 109831 RC 6th 08/27/2002 5 90053 122779 5th 08/28/2003 6 90053 124617 8th 05/07/2000 7 90097 145616 4th 06/06/2004 8 90097 164264 RC 6th 12/20/2001 9 90112 147688 7th 10/11/2000 10 90112 171989 9th 06/27/1999 11 90112 197925 RG 6th 02/23/2002 12 90142 150175 RC 6th 12/08/2001 13 90181 115847 RG 6th 03/09/2002 14 90181 192157 4th 05/23/2004 15 90270 150724 RG 6th 01/05/2002 16 90285 104967 GV 6th 04/21/2002 17 90285 121142 9th 04/20/1999 18 90285 157909 8th 06/09/2000 19 90437 136364 GV 6th 08/11/2002 20 90475 161166 RC 6th 08/11/2002 21 90497 115026 8th 12/10/1999 22 90497 123224 11th 06/20/1997 23 90497 138282 10th 08/19/1998 24 90497 182194 GV 6th 07/09/2002 25 90564 102087 7th 11/16/2000 26 90564 112176 10th 07/15/1998 27 90564 152677 11th 04/16/1997 28 90564 184266 GV 6th 01/28/2002 29 90570 126615 3rd 07/07/2005 30 90570 151125 GV 6th 09/06/2002 31 90570 161405 1st 10/23/2006 32 90570 164752 4th 07/08/2004 33 90609 122291 4th 04/14/2004 34 90609 125387 2nd 11/26/2005 35 90609 129168 1st 02/22/2007 36 90609 141678 RC 6th 05/13/2002 37 90609 188507 8th 09/17/2000 38 90617 140083 8th 01/30/2000 39 90617 171161 9th 06/30/1999 40 90617 199367 GV 6th 02/14/2002 41 90619 114192 GV 6th 01/22/2002 42 90619 189546 5th 11/12/2003 43 90636 154833 GV 6th 01/11/2002 44 90687 108609 8th 09/25/2000 45 90687 157517 9th 06/18/1999 46 90687 172160 GV 6th 04/22/2002 47 90694 121653 RC 6th 11/09/2001 48 90703 105870 GV 6th 06/04/2002 49 90725 108458 GV 6th 11/23/2001 50 90743 149356 8th 11/24/1999 51 90743 155638 RG 6th 09/16/2002 52 90758 139922 9th 03/09/1999 53 90758 145656 RG 6th 09/16/2002 54 90758 197632 8th 07/12/2000 55 90766 108342 RC 6th 06/05/2002 56 90766 116068 4th 08/11/2004 57 90766 175444 8th 05/04/2000 58 90844 165143 RG 6th 01/12/2002 59 90848 144110 GV 6th 07/19/2002 60 90851 131123 4th 04/03/2004 61 90851 140291 RG 6th 01/25/2002 62 90851 161810 3rd 05/25/2005 63 90862 107387 RG 6th 04/17/2002 64 90862 172943 8th 01/02/2000 65 90867 162410 GV 6th 07/31/2002 66 90878 142600 9th 06/12/1999 67 90878 167668 11th 03/06/1997 68 90878 176611 7th 11/14/2000 69 90878 181788 RC 6th 10/27/2001 70 90881 193854 4th 03/28/2004 71 90881 195960 RC 6th 12/25/2001 72 90941 102553 8th 09/03/2000 73 90941 183918 RG 6th 06/08/2002 74 90944 120066 4th 07/30/2004 75 90944 171456 RC 6th 08/09/2002 76 90944 196032 8th 07/31/2000 77 91016 111890 4th 04/08/2004 78 91016 117984 1st 12/25/2006 79 91016 146793 3rd 06/10/2005 80 91016 190014 GV 6th 06/08/2002 81 91058 123311 8th 11/23/1999 82 91058 162898 GV 6th 04/18/2002 83 91121 142468 RC 6th 05/17/2002 84 91171 106246 7th 10/07/2000 85 91171 184387 RG 6th 07/28/2002 86 91230 126750 RC 6th 04/03/2002 87 91275 184680 RC 6th 12/12/2001 88 91281 101920 5th 11/22/2003 89 91281 152295 3rd 05/02/2005 90 91281 161004 RC 6th 12/21/2001 91 91281 171374 8th 12/24/1999 92 91283 145273 RG 6th 10/04/2001 93 91283 155927 8th 01/11/2000 94 91315 115552 4th 03/11/2004 95 91315 185013 RC 6th 10/09/2001 96 91351 102807 RG 6th 06/05/2002 97 91351 166941 5th 09/15/2003 98 91373 111911 8th 02/12/2000 99 91373 148122 9th 04/27/1999 100 91373 171292 RC 6th 02/03/2002 101 91400 134072 RG 6th 11/12/2001 102 91405 128555 8th 12/06/1999 103 91405 155161 RG 6th 09/16/2002 104 91407 157367 RG 6th 12/20/2001 105 91421 131252 3rd 07/09/2005 106 91421 136035 8th 07/13/2000 107 91421 139059 RC 6th 02/03/2002 108 91421 192165 4th 06/10/2004 109 91432 164387 RG 6th 06/03/2002 110 91432 196439 8th 07/23/2000 111 91449 178627 RC 6th 05/12/2002 112 91451 137351 10th 09/26/1998 113 91451 138565 RG 6th 06/29/2002 114 91451 167545 8th 04/04/2000 115 91455 130249 RG 6th 08/13/2002 116 91455 160798 5th 12/02/2003 117 91462 125109 4th 06/20/2004 118 91462 157308 GV 6th 08/02/2002 119 91462 179664 8th 12/02/1999 120 91571 100025 8th 01/24/2000 121 91571 190753 RG 6th 12/16/2001 122 91587 129199 8th 07/27/2000 123 91587 152910 RG 6th 02/04/2002 124 91587 185592 10th 09/28/1998 125 91727 125111 7th 10/28/2000 126 91727 138951 RC 6th 04/07/2002 127 91749 143767 9th 05/07/1999 128 91749 149467 8th 08/04/2000 129 91749 168462 RG 6th 03/01/2002 130 91749 179029 5th 08/14/2003 131 91785 199093 GV 6th 05/19/2002 132 91865 154658 RC 6th 10/31/2001 133 91865 176517 5th 10/03/2003 134 91879 145733 RC 6th 11/16/2001 135 91888 146495 4th 02/17/2004 136 91888 159567 2nd 12/19/2005 137 91888 191643 GV 6th 09/16/2002 138 91903 127385 1st 09/27/2006 139 91903 138571 3rd 05/04/2005 140 91903 158368 GV 6th 09/19/2002 141 91903 196414 5th 10/06/2003 142 91913 196970 GV 6th 02/27/2002 143 91955 146373 8th 08/15/2000 144 91955 161913 RG 6th 04/26/2002 145 92001 116618 RC 6th 12/25/2001 146 92060 122594 8th 08/07/2000 147 92060 151934 10th 09/12/1998 148 92060 190033 RC 6th 09/12/2002 149 92073 148284 4th 04/16/2004 150 92073 180479 GV 6th 03/29/2002 151 92073 189488 1st 02/27/2007 152 92073 197931 3rd 01/06/2005 153 92078 116683 GV 6th 12/08/2001 154 92078 160020 8th 06/01/2000 155 92089 162884 RC 6th 03/17/2002 156 92089 172350 8th 05/23/2000 157 92112 107511 RC 6th 05/26/2002 158 92112 131528 8th 12/09/1999 159 92112 163121 9th 07/02/1999 160 92137 134779 RG 6th 05/20/2002 161 92194 145270 RC 6th 07/18/2002 162 92194 171626 8th 03/24/2000 163 92200 179019 RC 6th 05/12/2002 164 92214 107050 4th 06/17/2004 165 92214 117992 RC 6th 03/07/2002 166 92222 187030 4th 03/11/2004 167 92222 187899 8th 07/02/2000 168 92222 192301 GV 6th 11/02/2001 169 92222 195748 3rd 01/11/2005 170 92228 193660 GV 6th 10/10/2001 171 92243 104177 8th 02/21/2000 172 92243 154713 GV 6th 08/07/2002 173 92259 101606 GV 6th 07/01/2002 174 92259 140919 9th 05/25/1999 175 92259 168511 8th 04/10/2000 176 92259 171560 11th 08/29/1997 177 92291 172925 RC 6th 03/13/2002 178 92299 134911 GV 6th 03/26/2002 179 92511 105503 4th 07/04/2004 180 92511 123113 RC 6th 08/18/2002 181 92529 147821 RC 6th 07/31/2002 182 92529 164026 8th 07/01/2000 183 92533 128582 4th 04/08/2004 184 92533 167502 RC 6th 03/09/2002 185 92533 181441 2nd 11/03/2005 186 92556 166307 RC 6th 10/28/2001 187 92561 101967 3rd 03/21/2005 188 92561 110840 4th 04/02/2004 189 92561 146118 RG 6th 03/16/2002 190 92561 171972 1st 03/23/2007 191 92630 136825 GV 6th 05/08/2002 192 92660 107336 RG 6th 07/15/2002 193 92661 167979 RC 6th 03/17/2002 194 92683 138327 4th 02/19/2004 195 92683 149780 GV 6th 11/08/2001 196 92684 111864 GV 6th 09/23/2002 197 92684 199974 8th 12/11/1999 198 92711 160087 GV 6th 04/11/2002 199 92711 174472 4th 02/01/2004 200 92713 134059 4th 01/10/2004 201 92713 149890 RC 6th 08/19/2002 202 92748 181808 GV 6th 03/08/2002 203 92748 191552 5th 11/01/2003 204 92756 177436 RG 6th 01/14/2002 205 92820 178154 7th 11/01/2000 206 92820 190963 RC 6th 07/21/2002 207 92828 121844 RC 6th 01/16/2002 208 92828 132413 5th 10/27/2003 209 92869 139859 RC 6th 05/03/2002 210 92869 148518 8th 03/26/2000 211 92880 178465 RG 6th 07/05/2002 212 92880 199111 5th 11/12/2003 213 92889 135156 RG 6th 05/02/2002 214 93062 124509 GV 6th 06/10/2002 215 93065 122027 RG 6th 10/14/2001 216 93065 156332 8th 12/19/1999 217 93111 130951 4th 02/26/2004 218 93111 140756 3rd 09/01/2005 219 93111 184176 RG 6th 12/01/2001 220 93127 115429 3rd 02/18/2005 221 93127 130950 RG 6th 10/31/2001 222 93127 137090 4th 05/22/2004 223 93166 137241 5th 09/18/2003 224 93166 168609 GV 6th 08/16/2002 225 93210 186562 5th 08/26/2003 226 93210 188558 RC 6th 04/01/2002 227 93244 104332 RG 6th 12/17/2001 228 93244 152974 4th 04/12/2004 229 93270 180792 RG 6th 03/05/2002 230 93271 114749 RG 6th 11/21/2001 231 93271 169519 8th 02/14/2000 232 93279 142768 GV 6th 01/16/2002 233 93279 180740 8th 01/01/2000 234 93287 103536 4th 02/01/2004 235 93287 125868 GV 6th 12/16/2001 236 93287 149723 9th 11/28/1998 237 93287 191672 8th 04/23/2000 238 93315 135539 RG 6th 05/25/2002 239 93373 100621 8th 06/09/2000 240 93373 102566 RC 6th 07/15/2002 241 93376 170276 4th 05/31/2004 242 93376 186047 RG 6th 06/01/2002 243 93380 124669 GV 6th 08/13/2002 244 93380 133645 4th 01/14/2004 245 93396 106181 7th 10/18/2000 246 93396 179554 RC 6th 03/25/2002 247 93410 103036 8th 04/09/2000 248 93410 135382 GV 6th 03/23/2002 249 93427 127115 5th 11/28/2003 250 93427 127335 3rd 08/19/2005 251 93427 180003 RC 6th 08/20/2002 252 93480 101222 RC 6th 07/09/2002 253 93496 170271 RC 6th 04/22/2002 254 93503 109733 4th 06/21/2004 255 93503 199662 GV 6th 09/08/2002 256 93534 131717 4th 03/08/2004 257 93534 177705 RC 6th 12/10/2001 258 93543 111117 RC 6th 11/06/2001 259 93543 139246 5th 09/07/2003 260 93566 140046 4th 03/18/2004 261 93566 168785 RC 6th 08/17/2002 262 93584 175216 7th 11/06/2000 263 93584 187548 RG 6th 04/17/2002 264 93609 119315 5th 11/07/2003 265 93609 184587 RG 6th 07/08/2002 266 93624 133618 RG 6th 02/20/2002 267 93624 143935 10th 09/01/1998 268 93624 171090 7th 10/16/2000 269 93637 160205 RC 6th 07/15/2002 270 93637 175830 4th 01/01/2004 271 93639 138569 RC 6th 07/23/2002 272 93639 193853 8th 03/19/2000 273 93639 194971 9th 05/12/1999 274 93648 165398 RG 6th 06/22/2002 275 93648 195166 8th 12/06/1999 276 93676 133120 4th 01/11/2004 277 93676 191046 RC 6th 07/16/2002 278 93705 107518 RC 6th 02/26/2002 279 93705 148554 5th 11/25/2003 280 93705 189808 8th 03/05/2000 281 93705 191689 9th 11/19/1998 282 93705 192405 3rd 04/17/2005 283 93719 113258 9th 10/07/1998 284 93719 151571 8th 02/26/2000 285 93719 187861 RG 6th 06/08/2002 286 93719 195169 4th 05/19/2004 287 93826 136721 10th 09/22/1998 288 93826 139621 RC 6th 06/17/2002 289 93826 155732 8th 04/22/2000 290 93859 171526 8th 05/08/2000 291 93859 172338 RG 6th 01/05/2002 292 93871 100964 RG 6th 02/17/2002 293 93871 164783 8th 08/23/2000 294 93902 106186 RC 6th 07/29/2002 295 93904 148418 RC 6th 06/10/2002 296 93904 196035 5th 11/18/2003 297 93915 122108 GV 6th 03/03/2002 298 93927 188289 RC 6th 08/12/2002 299 93968 103892 4th 08/04/2004 300 93968 145961 3rd 01/29/2005 301 93968 194119 RG 6th 05/17/2002 302 93989 102301 4th 04/16/2004 303 93989 102730 RC 6th 04/07/2002 304 94005 135839 RG 6th 06/10/2002 305 94026 124002 7th 10/09/2000 306 94026 151933 RC 6th 09/29/2002 307 94035 141577 3rd 05/26/2005 308 94035 189718 1st 05/04/2007 309 94035 190484 K 10/30/2007 310 94035 194162 4th 05/19/2004 311 94035 197823 RG 6th 11/21/2001 312 94041 130471 8th 07/24/2000 313 94041 141536 RG 6th 06/09/2002 314 94041 141762 2nd 11/12/2005 315 94041 175438 4th 03/15/2004 316 94060 173646 GV 6th 03/14/2002 317 94094 161044 RC 6th 10/08/2001 318 94108 109661 8th 04/08/2000 319 94108 161811 4th 06/21/2004 320 94108 163180 3rd 01/07/2005 321 94108 171544 RC 6th 04/26/2002 322 94201 108869 4th 04/13/2004 323 94201 132884 3rd 04/03/2005 324 94201 172053 RG 6th 03/12/2002 325 94201 189228 8th 09/24/2000 326 94237 197314 GV 6th 09/30/2002 327 94283 159849 4th 01/17/2004 328 94283 192937 RG 6th 10/30/2001 329 94284 147278 RC 6th 08/18/2002 330 94284 192604 4th 04/06/2004 331 94331 114366 RC 6th 12/04/2001 332 94372 111243 8th 07/30/2000 333 94372 118158 RC 6th 08/10/2002 334 94372 173294 11th 07/04/1997 335 94372 190972 9th 06/04/1999 336 94395 106090 4th 05/06/2004 337 94395 116183 RC 6th 03/09/2002 338 94395 140884 8th 08/18/2000 339 94444 173280 RC 6th 01/16/2002 340 94488 114949 GV 6th 01/09/2002 341 94488 165449 8th 12/06/1999 342 94488 198716 9th 12/29/1998 343 94506 123982 8th 05/05/2000 344 94506 171080 RG 6th 02/22/2002 345 94545 138244 8th 01/18/2000 346 94545 150225 RC 6th 05/25/2002 347 94545 189457 4th 01/19/2004 348 94608 133987 4th 05/09/2004 349 94608 152593 RC 6th 01/21/2002 350 94608 198138 8th 01/23/2000 351 94671 107688 5th 10/02/2003 352 94671 191894 RC 6th 08/22/2002 353 94700 132765 GV 6th 01/07/2002 354 94756 173748 RG 6th 06/19/2002 355 94760 128025 RC 6th 01/25/2002 356 94760 149305 7th 10/23/2000 357 94790 161695 RC 6th 11/24/2001 358 94790 192779 8th 11/25/1999 359 94800 127296 3rd 05/23/2005 360 94800 132667 RG 6th 05/28/2002 361 94800 135663 4th 02/01/2004 362 94800 176609 8th 04/04/2000 363 94837 130269 RG 6th 03/27/2002 364 94837 144996 4th 05/22/2004 365 94837 145677 3rd 01/24/2005 366 94873 117575 GV 6th 08/16/2002 367 94873 178959 8th 09/24/2000 368 94880 125344 GV 6th 08/04/2002 369 94880 131386 8th 01/28/2000 370 94883 126617 RC 6th 02/11/2002 371 94934 130669 7th 10/05/2000 372 94934 155212 RG 6th 05/05/2002 373 94959 108980 4th 07/29/2004 374 94959 117348 3rd 08/02/2005 375 94959 153903 RG 6th 12/30/2001 376 94974 154467 RG 6th 07/16/2002 377 94982 116222 GV 6th 07/24/2002 378 94982 192953 5th 11/19/2003 379 95004 119639 8th 02/04/2000 380 95004 177683 RG 6th 12/12/2001 381 95061 153068 GV 6th 06/30/2002 382 95082 118765 8th 08/26/2000 383 95082 122236 RG 6th 07/30/2002 384 95084 137874 GV 6th 12/09/2001 385 95084 199065 7th 10/25/2000 386 95127 140293 GV 6th 08/24/2002 387 95147 137286 3rd 07/24/2005 388 95147 139970 GV 6th 04/14/2002 389 95147 169781 4th 07/03/2004 390 95185 162960 RC 6th 06/20/2002 391 95206 163527 GV 6th 06/01/2002 392 95244 105928 4th 04/18/2004 393 95244 156564 RC 6th 05/21/2002 394 95246 113308 4th 04/21/2004 395 95246 140428 GV 6th 12/07/2001 396 95258 123967 RG 6th 06/15/2002 397 95295 118131 4th 04/11/2004 398 95295 125260 7th 11/07/2000 399 95295 160365 GV 6th 03/20/2002 400 95320 106992 RG 6th 01/14/2002 401 95320 168435 5th 09/06/2003 402 95320 171906 3rd 04/22/2005 403 95340 100251 9th 04/02/1999 404 95340 166148 GV 6th 06/15/2002 405 95340 181660 8th 08/04/2000 406 95349 120911 RC 6th 02/27/2002 407 95349 196975 8th 07/10/2000 408 95416 112731 RG 6th 06/29/2002 409 95416 154238 8th 07/06/2000 410 95503 131511 4th 07/27/2004 411 95503 157661 RC 6th 05/24/2002 412 95506 130651 RC 6th 02/06/2002 413 95530 117659 RC 6th 09/11/2002 414 95530 118427 8th 02/12/2000 415 95530 185700 9th 04/04/1999 416 95537 131488 4th 06/24/2004 417 95537 166430 RC 6th 01/02/2002 418 95537 172095 8th 03/12/2000 419 95537 175442 9th 01/04/1999 420 95538 151832 4th 03/18/2004 421 95538 187597 RC 6th 09/02/2002 422 95539 136608 GV 6th 04/09/2002 423 95539 144080 4th 12/27/2003 424 95559 113500 3rd 03/30/2005 425 95559 168523 RG 6th 01/14/2002 426 95559 189593 4th 04/20/2004 427 95621 159363 RC 6th 04/15/2002 428 95697 160212 RC 6th 05/26/2002 429 95737 110146 4th 06/19/2004 430 95737 137768 RG 6th 06/13/2002 431 95789 101347 8th 01/24/2000 432 95789 110616 9th 01/21/1999 433 95789 121523 11th 08/29/1997 434 95789 126150 GV 6th 04/29/2002 435 95815 142204 9th 02/15/1999 436 95815 168111 RC 6th 08/10/2002 437 95815 186514 8th 01/10/2000 438 95843 129414 4th 06/13/2004 439 95843 130700 RC 6th 12/14/2001 440 95879 128065 GV 6th 02/06/2002 441 95915 131319 RC 6th 01/01/2002 442 95915 170945 8th 12/23/1999 443 95915 186669 5th 09/18/2003 444 95922 179917 RC 6th 03/01/2002 445 96045 107198 4th 03/11/2004 446 96045 109435 GV 6th 01/02/2002 447 96045 114494 10th 09/12/1998 448 96045 156291 8th 04/11/2000 449 96086 114908 10th 08/21/1998 450 96086 129416 GV 6th 07/02/2002 451 96086 132981 8th 08/05/2000 452 96086 183520 4th 01/12/2004 453 96089 118261 8th 01/10/2000 454 96089 138398 RC 6th 04/14/2002 455 96113 163105 4th 04/11/2004 456 96113 199668 RG 6th 01/02/2002 457 96120 102167 3rd 05/05/2005 458 96120 133586 5th 08/27/2003 459 96120 178925 RC 6th 05/25/2002 460 96133 109790 11th 03/09/1997 461 96133 174069 9th 02/13/1999 462 96133 185349 8th 02/03/2000 463 96133 187446 RC 6th 08/11/2002 464 96152 102827 GV 6th 06/05/2002 465 96152 182933 5th 08/24/2003 466 96300 156007 8th 04/15/2000 467 96300 179216 4th 07/30/2004 468 96300 180431 RG 6th 01/02/2002 469 96401 176148 5th 10/06/2003 470 96401 198173 GV 6th 01/30/2002 471 96415 150761 RG 6th 09/03/2002 472 96415 196804 5th 10/02/2003 473 96416 128644 8th 02/08/2000 474 96416 193337 RG 6th 07/09/2002 475 96429 190415 GV 6th 08/08/2002 476 96447 104221 2nd 07/29/2006 477 96447 180209 2nd 10/03/2005 478 96447 180721 GV 6th 04/22/2002 479 96447 196908 5th 09/05/2003 480 96453 151898 8th 09/21/2000 481 96453 192495 RC 6th 06/17/2002 482 96555 113366 GV 6th 05/27/2002 483 96555 134847 9th 01/23/1999 484 96555 143217 8th 12/28/1999 485 96594 148539 RC 6th 03/25/2002 486 96594 179197 8th 08/24/2000 487 96607 160799 RC 6th 01/10/2002 488 96609 112764 RC 6th 05/23/2002 489 96609 181328 5th 11/28/2003 490 96616 191626 RG 6th 01/06/2002 491 96673 134170 GV 6th 06/12/2002 492 96673 194324 7th 10/20/2000 493 96674 165121 RC 6th 08/30/2002 494 96677 186217 RG 6th 04/12/2002 495 96722 118858 RC 6th 05/11/2002 496 96722 141179 5th 09/10/2003 497 96788 143453 RC 6th 11/11/2001 498 96799 139554 GV 6th 09/07/2002 499 96800 131250 GV 6th 04/10/2002 500 96800 187456 8th 05/27/2000 501 96851 132512 9th 06/06/1999 502 96851 143109 GV 6th 03/18/2002 503 96851 158860 8th 08/12/2000 504 96851 198977 4th 12/28/2003 505 96858 121242 4th 06/03/2004 506 96858 128250 RC 6th 06/19/2002 507 96889 104910 4th 01/06/2004 508 96889 117856 RG 6th 11/24/2001 509 96889 122395 3rd 05/29/2005 510 96889 176982 7th 10/16/2000 511 96915 111359 RC 6th 05/27/2002 512 96915 148326 4th 04/06/2004 513 96915 186020 3rd 09/15/2005 514 96947 127873 4th 01/28/2004 515 96947 139165 GV 6th 03/23/2002 516 96950 103086 RG 6th 05/16/2002 517 96950 161034 4th 12/29/2003 518 96951 137803 RG 6th 07/18/2002 519 96951 138284 8th 03/27/2000 520 97008 180001 8th 06/02/2000 521 97008 183727 RC 6th 01/08/2002 522 97042 118167 RG 6th 03/01/2002 523 97091 167038 8th 03/13/2000 524 97091 183926 RC 6th 08/18/2002 525 97129 126451 RG 6th 10/12/2001 526 97129 166060 8th 04/10/2000 527 97132 156101 4th 01/14/2004 528 97132 166034 RC 6th 09/05/2002 529 97132 183236 8th 06/25/2000 530 97132 192254 9th 03/30/1999 531 97175 114141 9th 11/08/1998 532 97175 119200 RC 6th 11/07/2001 533 97175 148743 8th 05/26/2000 534 97202 127996 RC 6th 01/20/2002 535 97202 135921 11th 06/27/1997 536 97202 169608 7th 10/16/2000 537 97202 189235 9th 06/07/1999 538 97203 146234 RG 6th 12/26/2001 539 97233 111037 4th 02/26/2004 540 97233 148320 2nd 05/19/2006 541 97233 157353 RG 6th 10/30/2001 542 97233 175515 3rd 01/15/2005 543 97233 188538 8th 08/10/2000 544 97260 180588 GV 6th 12/14/2001 545 97341 177949 RC 6th 08/31/2002 546 97344 191830 RG 6th 01/28/2002 547 97363 110425 3rd 05/10/2005 548 97363 146665 1st 12/03/2006 549 97363 166899 8th 07/04/2000 550 97363 169986 5th 08/30/2003 551 97363 199657 GV 6th 07/06/2002 552 97411 101924 RC 6th 07/30/2002 553 97411 146871 8th 03/21/2000 554 97418 103792 RC 6th 08/26/2002 555 97418 127586 1st 02/12/2007 556 97418 145859 3rd 04/18/2005 557 97418 187765 4th 03/14/2004 558 97419 103595 8th 07/22/2000 559 97419 109360 9th 12/10/1998 560 97419 147994 5th 09/16/2003 561 97419 171095 RC 6th 01/27/2002 562 97419 185149 10th 01/10/1998 563 97421 135378 8th 02/08/2000 564 97421 150297 RC 6th 04/02/2002 565 97432 151380 RC 6th 11/27/2001 566 97456 146700 5th 11/29/2003 567 97456 189021 GV 6th 05/08/2002 568 97475 105179 4th 01/03/2004 569 97475 121696 3rd 02/05/2005 570 97475 190568 RG 6th 10/17/2001 571 97523 180381 RG 6th 01/02/2002 572 97527 123139 4th 02/12/2004 573 97527 133925 3rd 02/09/2005 574 97527 169204 RC 6th 12/24/2001 575 97589 116374 8th 04/15/2000 576 97589 193649 RC 6th 02/20/2002 577 97605 189925 GV 6th 02/28/2002 578 97613 179248 8th 07/09/2000 579 97613 197844 RC 6th 01/26/2002 580 97639 107660 4th 02/12/2004 581 97639 157651 RG 6th 08/08/2002 582 97668 180869 RC 6th 06/01/2002 583 97705 149730 8th 03/31/2000 584 97705 163185 GV 6th 12/09/2001 585 97716 112428 3rd 09/12/2005 586 97716 146033 5th 11/18/2003 587 97716 171305 RC 6th 04/16/2002 588 97751 116660 5th 10/05/2003 589 97751 140373 10th 08/05/1998 590 97751 163930 8th 09/09/2000 591 97751 173688 RC 6th 11/25/2001 592 97772 195989 RG 6th 09/22/2002 593 97800 128802 5th 11/29/2003 594 97800 140512 RC 6th 06/22/2002 595 97800 156023 2nd 11/01/2005 596 97823 150235 4th 12/15/2003 597 97823 177377 RC 6th 10/17/2001 598 97881 136431 9th 02/04/1999 599 97881 137912 RG 6th 03/14/2002 600 97881 180864 8th 09/10/2000 601 97908 173919 5th 11/25/2003 602 97908 190692 RC 6th 06/18/2002 603 97908 195060 2nd 11/03/2005 604 97915 174718 RC 6th 04/23/2002 605 97931 105262 RG 6th 02/02/2002 606 97931 124486 4th 07/30/2004 607 97957 101238 8th 03/26/2000 608 97957 145886 4th 04/05/2004 609 97957 198040 RG 6th 11/01/2001 610 97974 119193 RG 6th 09/17/2002 611 98002 141849 RC 6th 10/22/2001 612 98037 101759 8th 02/20/2000 613 98037 139106 RC 6th 07/25/2002 614 98040 183813 RG 6th 11/14/2001 615 98060 196766 GV 6th 12/09/2001 616 98084 102714 GV 6th 08/12/2002 617 98084 113997 11th 04/15/1997 618 98084 126989 10th 09/18/1998 619 98084 131093 5th 09/04/2003 620 98084 176901 12th 04/03/1996 621 98084 191333 8th 04/19/2000 622 98203 191378 RG 6th 02/23/2002 623 98259 100426 5th 10/27/2003 624 98259 158309 8th 01/04/2000 625 98259 158559 GV 6th 02/10/2002 626 98319 105576 8th 06/02/2000 627 98319 166308 GV 6th 06/13/2002 628 98319 197738 9th 06/02/1999 629 98336 128945 4th 02/13/2004 630 98336 168552 8th 09/04/2000 631 98336 183494 RG 6th 09/04/2002 632 98338 100487 4th 03/20/2004 633 98338 101041 RG 6th 04/19/2002 634 98353 134442 RC 6th 09/07/2002 635 98354 129467 8th 07/03/2000 636 98354 179725 RC 6th 03/11/2002 637 98394 100771 4th 02/23/2004 638 98394 101523 RC 6th 01/17/2002 639 98400 122415 8th 06/29/2000 640 98400 163364 RG 6th 12/01/2001 641 98418 134907 8th 09/24/2000 642 98418 146894 4th 06/21/2004 643 98418 192032 RC 6th 09/08/2002 644 98434 111134 4th 02/02/2004 645 98434 113442 2nd 08/09/2006 646 98434 124490 RG 6th 07/27/2002 647 98434 130532 8th 08/12/2000 648 98434 196283 3rd 03/21/2005 649 98454 106420 8th 12/06/1999 650 98454 153672 GV 6th 04/20/2002 651 98468 113928 RG 6th 08/24/2002 652 98468 117342 5th 08/15/2003 653 98468 182130 3rd 02/21/2005 654 98547 136333 RG 6th 05/06/2002 655 98553 129994 RG 6th 11/08/2001 656 98553 136471 3rd 08/14/2005 657 98553 161460 5th 10/11/2003 658 98553 190691 8th 04/07/2000 659 98564 110434 8th 09/24/2000 660 98564 148852 GV 6th 05/07/2002 661 98564 177570 5th 10/14/2003 662 98584 173222 GV 6th 10/06/2001 663 98624 103965 9th 11/28/1998 664 98624 152804 GV 6th 09/23/2002 665 98624 165565 8th 03/23/2000 666 98640 103933 5th 12/02/2003 667 98640 120675 3rd 05/25/2005 668 98640 182857 RC 6th 05/12/2002 669 98679 108542 RC 6th 03/09/2002 670 98679 145353 4th 03/24/2004 671 98684 161452 RG 6th 07/11/2002 672 98688 127827 GV 6th 02/22/2002 673 98701 167389 GV 6th 11/22/2001 674 98701 198764 8th 02/18/2000 675 98701 198961 10th 08/01/1998 676 98716 122556 7th 11/16/2000 677 98716 146119 5th 08/26/2003 678 98716 165303 RC 6th 12/07/2001 679 98716 188861 9th 11/10/1998 680 98724 105670 RG 6th 02/02/2002 681 98724 123745 8th 04/19/2000 682 98780 122933 8th 02/16/2000 683 98780 133318 GV 6th 10/20/2001 684 98781 101103 4th 07/28/2004 685 98781 126282 RC 6th 10/01/2001 686 98781 127186 3rd 08/31/2005 687 98865 122421 RC 6th 03/04/2002 688 98865 132131 8th 04/14/2000 689 98892 107356 8th 04/30/2000 690 98892 169514 RC 6th 06/28/2002 691 98897 149401 RC 6th 11/02/2001 692 98903 127363 GV 6th 02/11/2002 693 98903 166806 9th 02/06/1999 694 98903 194697 8th 07/31/2000 695 98904 115172 RC 6th 03/07/2002 696 98904 170203 5th 10/20/2003 697 98935 100535 RC 6th 02/13/2002 698 98935 130443 10th 09/10/1998 699 98935 167785 7th 10/29/2000 700 98939 169529 RG 6th 11/18/2001 701 98951 104588 RG 6th 05/13/2002 702 98961 195022 RC 6th 04/18/2002 703 98983 108935 4th 07/28/2004 704 98983 114343 RC 6th 08/21/2002 705 98983 179303 3rd 05/12/2005 706 99028 126527 RC 6th 06/03/2002 707 99056 105598 8th 02/05/2000 708 99056 132344 RG 6th 01/20/2002 709 99056 134533 4th 05/11/2004 710 99056 135524 9th 03/29/1999 711 99101 181497 5th 09/02/2003 712 99101 183318 RG 6th 04/25/2002 713 99203 185342 GV 6th 12/13/2001 714 99214 143670 RC 6th 09/05/2002 715 99214 178654 7th 10/05/2000 716 99216 114704 RG 6th 11/16/2001 717 99216 183729 4th 12/28/2003 718 99216 190937 7th 11/06/2000 719 99232 118477 10th 07/14/1998 720 99232 141118 RC 6th 02/28/2002 721 99232 198177 8th 08/19/2000 722 99273 146829 GV 6th 06/29/2002 723 99296 137503 RC 6th 01/27/2002 724 99310 165242 RC 6th 05/30/2002 725 99322 119004 5th 10/26/2003 726 99322 170445 8th 12/27/1999 727 99322 193894 RC 6th 04/08/2002 728 99348 128400 8th 12/27/1999 729 99348 135956 GV 6th 03/09/2002 730 99393 121098 4th 02/01/2004 731 99393 133467 RC 6th 06/04/2002 732 99411 140232 RC 6th 11/16/2001 733 99480 182378 RC 6th 04/19/2002 734 99514 123862 RC 6th 05/17/2002 735 99617 108526 5th 09/06/2003 736 99617 110675 8th 03/30/2000 737 99617 140983 3rd 03/15/2005 738 99617 184368 RC 6th 04/18/2002 739 99648 104280 4th 07/05/2004 740 99648 171912 RC 6th 06/21/2002 741 99649 187840 RG 6th 02/23/2002 742 99667 114318 8th 12/06/1999 743 99667 132894 5th 10/30/2003 744 99667 172130 RC 6th 09/15/2002 745 99692 139343 8th 04/06/2000 746 99692 199312 RC 6th 02/15/2002 747 99723 139603 RC 6th 02/15/2002 748 99738 114941 RG 6th 01/04/2002 749 99738 129164 8th 02/11/2000 750 99738 173845 10th 08/11/1998 751 99738 182104 11th 03/28/1997 752 99742 139833 RC 6th 02/21/2002 753 99742 179417 4th 03/26/2004 754 99806 107293 RC 6th 09/12/2002 755 99806 127614 5th 08/19/2003 756 99825 117707 RC 6th 08/11/2002 757 99825 142815 2nd 06/05/2006 758 99825 166896 5th 09/03/2003 759 99825 190016 3rd 09/21/2005 760 99829 100320 RG 6th 05/06/2002 761 99829 142212 4th 04/26/2004 762 99850 140538 3rd 06/26/2005 763 99850 143585 RC 6th 04/17/2002 764 99850 174210 7th 10/13/2000 765 99850 178333 4th 12/22/2003 766 99864 139609 RG 6th 05/04/2002 767 99864 191438 7th 10/07/2000 768 99878 131795 RC 6th 05/03/2002 769 99878 175185 9th 11/11/1998 770 99878 185135 8th 07/21/2000 771 99882 108139 8th 12/09/1999 772 99882 172083 RG 6th 02/07/2002 773 99882 196885 5th 12/10/2003 774 99889 169148 RG 6th 03/30/2002 775 99901 143747 RC 6th 02/19/2002 776 99943 115577 GV 6th 09/17/2002 777 99943 141838 2nd 10/06/2005 778 99943 148815 4th 03/12/2004</CODE></PRE><P>I pulled out the sixth graders so that I have one observation for each sixth grader as the assignment instructed in a previous question:&nbsp;</P><P>&nbsp;</P><PRE><CODE class=" language-sas">Sixth Graders Taken Out Obs Family_id Student_ID School6 Grade6 SixthGDOB zero 1 90021 127945 RG 6th 01/26/2002 2 90053 109831 RC 6th 08/27/2002 3 90097 164264 RC 6th 12/20/2001 4 90112 197925 RG 6th 02/23/2002 5 90142 150175 RC 6th 12/08/2001 6 90181 115847 RG 6th 03/09/2002 7 90270 150724 RG 6th 01/05/2002 8 90285 104967 GV 6th 04/21/2002 9 90437 136364 GV 6th 08/11/2002 10 90475 161166 RC 6th 08/11/2002 11 90497 182194 GV 6th 07/09/2002 12 90564 184266 GV 6th 01/28/2002 13 90570 151125 GV 6th 09/06/2002 14 90609 141678 RC 6th 05/13/2002 15 90617 199367 GV 6th 02/14/2002 16 90619 114192 GV 6th 01/22/2002 17 90636 154833 GV 6th 01/11/2002 18 90687 172160 GV 6th 04/22/2002 19 90694 121653 RC 6th 11/09/2001 20 90703 105870 GV 6th 06/04/2002 21 90725 108458 GV 6th 11/23/2001 22 90743 155638 RG 6th 09/16/2002</CODE></PRE><P>I used this code:&nbsp;</P><PRE>Data SixthGrade (rename = (DOB = SixthGDOB Grade=Grade6 School=School6));<BR />Set schoolsurvey; <BR />if School NE zero;<BR />run; <BR /><BR />proc sort data = sixthgrade;<BR />by family_ID Student_ID;<BR />Run;<BR /><BR />Proc print data = sixthgrade;<BR />Title "Sixth Graders Taken Out";<BR />Run; </PRE><P>&nbsp;</P><P>My issue is merging those two data sets (Orginal &amp; the one with only 6 graders). I have no clue how I would program SAS to read the next observation as I could do in Excel. I would like SAS to check if the Family_ID is the same as another observation that shares this Family_ID then place the DOB of the observation that has a school value or grade matches with sixth grade.&nbsp;&nbsp;</P> Tue, 15 Oct 2019 04:52:55 GMT https://communities.sas.com/t5/SAS-Studio/How-do-I-combine-two-data-sets-Primary-Key-has-multiple-unique/m-p/596430#M8144 petergomillion 2019-10-15T04:52:55Z Re: How do I combine two data sets (Primary Key has multiple unique observations) https://communities.sas.com/t5/SAS-Studio/How-do-I-combine-two-data-sets-Primary-Key-has-multiple-unique/m-p/596435#M8145 <P>There are many ways how you can combine data. Below uses a data step MERGE.</P> <PRE><CODE class=" language-sas">proc sort data = schoolsurvey; by family_ID Student_ID; Run; data SixthGrade; Set schoolsurvey; by family_ID Student_ID; if not missing(School); rename DOB = SixthGDOB Grade=Grade6 School=School6 Student_ID=Student_ID6; run; /* check for duplicates to avoid m:m relationships when merging data later on */ proc sort data=SixthGrade nodupkey out=SixthGrade dupout=SixthGrade_Duplicates; by family_ID; run; title 'duplicates'; proc print data=SixthGrade_Duplicates; run; title; data combined; merge schoolsurvey SixthGrade; by family_ID; run; title 'combined data'; proc print; run; title;</CODE></PRE> Tue, 15 Oct 2019 05:50:32 GMT https://communities.sas.com/t5/SAS-Studio/How-do-I-combine-two-data-sets-Primary-Key-has-multiple-unique/m-p/596435#M8145 Patrick 2019-10-15T05:50:32Z Re: How do I combine two data sets (Primary Key has multiple unique observations) https://communities.sas.com/t5/SAS-Studio/How-do-I-combine-two-data-sets-Primary-Key-has-multiple-unique/m-p/596438#M8146 <P>I read your data with</P> <PRE><CODE class=" language-sas">data have; infile datalines dlm='09'x dsd truncover; input family_id $ student_id $ school6 $ grade $ dob :mmddyy10.; format dob mmddyy10.; datalines;</CODE></PRE> <P>Then I ran this against the resulting dataset:</P> <PRE><CODE class=" language-sas">proc sort data=have; by family_id; run; data want; merge have ( in=orig drop=school6 where=(grade ne '6th') ) have ( in=sixth keep=family_id school6 grade dob rename=(grade=grade_6 dob=dob_6) where=(grade_6 = '6th') ) ; by family_id; if orig and sixth; drop grade_6; run; proc print data=want (obs=10) noobs; run;</CODE></PRE> <P>to get this result:</P> <PRE>family_ student_ id id grade dob school6 dob_6 90021 103699 4th 07/24/2004 RG 01/26/2002 90021 149229 2nd 10/28/2005 RG 01/26/2002 90053 122779 5th 08/28/2003 RC 08/27/2002 90053 124617 8th 05/07/2000 RC 08/27/2002 90097 145616 4th 06/06/2004 RC 12/20/2001 90112 147688 7th 10/11/2000 RG 02/23/2002 90112 171989 9th 06/27/1999 RG 02/23/2002 90181 192157 4th 05/23/2004 RG 03/09/2002 90285 121142 9th 04/20/1999 GV 04/21/2002 90285 157909 8th 06/09/2000 GV 04/21/2002 </PRE> Tue, 15 Oct 2019 06:52:49 GMT https://communities.sas.com/t5/SAS-Studio/How-do-I-combine-two-data-sets-Primary-Key-has-multiple-unique/m-p/596438#M8146 Kurt_Bremser 2019-10-15T06:52:49Z Re: How do I combine two data sets (Primary Key has multiple unique observations) https://communities.sas.com/t5/SAS-Studio/How-do-I-combine-two-data-sets-Primary-Key-has-multiple-unique/m-p/596628#M8149 <P>Thank you so much for your reply. I will check these solutions as soon as I make it home this evening.&nbsp;</P> Tue, 15 Oct 2019 19:06:19 GMT https://communities.sas.com/t5/SAS-Studio/How-do-I-combine-two-data-sets-Primary-Key-has-multiple-unique/m-p/596628#M8149 petergomillion 2019-10-15T19:06:19Z Re: How do I combine two data sets (Primary Key has multiple unique observations) https://communities.sas.com/t5/SAS-Studio/How-do-I-combine-two-data-sets-Primary-Key-has-multiple-unique/m-p/596720#M8152 <P>Thanks! I was able to successfully run your program which gave me the results that I need.&nbsp;</P> Wed, 16 Oct 2019 02:49:36 GMT https://communities.sas.com/t5/SAS-Studio/How-do-I-combine-two-data-sets-Primary-Key-has-multiple-unique/m-p/596720#M8152 petergomillion 2019-10-16T02:49:36Z