I am trying to append around ~35 excel files into 1 dta files. Luckily all the files are cleaned and mirror one another in terms of how they are structured. I figured because of this I can loop a code to append all of the files for me. All of the files are named ENR_YEAR_MONTH.xlsx so a sample coould potentially be named ENR_2017_01.xlsx.
My first step was to try my code out to get my desired result (to ensure it works):
Code:
set more off tempfile in import excel "ENR_2016_01.xlsx", sheet("MCHIP") cellrange(A10:K103) firstrow case(lower) clear rename a county_name drop anthem mdwise mhs total f g drop mchip schip gen medicaid_enroll_ch= county+ expansion gen month="01" gen year="2016" save `in', replace
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str15 county_name long county int expansion float medicaid_enroll_ch str2 month str4 year "01-ADAMS " 342 39 381 "01" "2016" "02-ALLEN " 5666 453 6119 "01" "2016" "03-BARTHOLOMEW " 815 81 896 "01" "2016" "04-BENTON " 121 10 131 "01" "2016" "05-BLACKFORD " 184 16 200 "01" "2016" "06-BOONE " 515 50 565 "01" "2016" "07-BROWN " 209 20 229 "01" "2016" "08-CARROLL " 263 32 295 "01" "2016" "09-CASS " 683 36 719 "01" "2016" "10-CLARK " 1619 133 1752 "01" "2016" "11-CLAY " 405 34 439 "01" "2016" "12-CLINTON " 562 53 615 "01" "2016" "13-CRAWFORD " 113 5 118 "01" "2016" "14-DAVIESS " 496 61 557 "01" "2016" "15-DEARBORN " 471 46 517 "01" "2016" "16-DECATUR " 344 40 384 "01" "2016" "17-DEKALB " 584 48 632 "01" "2016" "18-DELAWARE " 1267 110 1377 "01" "2016" "19-DUBOIS " 367 42 409 "01" "2016" "20-ELKHART " 4358 398 4756 "01" "2016" "21-FAYETTE " 435 28 463 "01" "2016" "22-FLOYD " 815 74 889 "01" "2016" "23-FOUNTAIN " 174 4 178 "01" "2016" "24-FRANKLIN " 284 12 296 "01" "2016" "25-FULTON " 349 42 391 "01" "2016" "26-GIBSON " 344 25 369 "01" "2016" "27-GRANT " 949 78 1027 "01" "2016" "28-GREENE " 468 48 516 "01" "2016" "29-HAMILTON " 2374 283 2657 "01" "2016" "30-HANCOCK " 712 71 783 "01" "2016" "31-HARRISON " 468 54 522 "01" "2016" "32-HENDRICKS " 1514 184 1698 "01" "2016" "33-HENRY " 746 72 818 "01" "2016" "34-HOWARD " 908 63 971 "01" "2016" "35-HUNTINGTON " 456 37 493 "01" "2016" "36-JACKSON " 507 35 542 "01" "2016" "37-JASPER " 381 44 425 "01" "2016" "38-JAY " 334 26 360 "01" "2016" "39-JEFFERSON " 356 40 396 "01" "2016" "40-JENNINGS " 390 37 427 "01" "2016" "41-JOHNSON " 1891 185 2076 "01" "2016" "42-KNOX " 449 42 491 "01" "2016" "43-KOSCIUSKO " 1182 109 1291 "01" "2016" "44-LAGRANGE " 383 32 415 "01" "2016" "45-LAKE " 5608 469 6077 "01" "2016" "46-LAPORTE " 1339 105 1444 "01" "2016" "47-LAWRENCE " 745 54 799 "01" "2016" "48-MADISON " 1750 128 1878 "01" "2016" "49-MARION " 18125 1102 19227 "01" "2016" "50-MARSHALL " 923 74 997 "01" "2016" "51-MARTIN " 150 7 157 "01" "2016" "52-MIAMI " 464 26 490 "01" "2016" "53-MONROE " 1164 158 1322 "01" "2016" "54-MONTGOMERY " 383 34 417 "01" "2016" "55-MORGAN " 916 68 984 "01" "2016" "56-NEWTON " 171 14 185 "01" "2016" "57-NOBLE " 696 62 758 "01" "2016" "58-OHIO " 26 3 29 "01" "2016" "59-ORANGE " 378 33 411 "01" "2016" "60-OWEN " 401 42 443 "01" "2016" "61-PARKE " 215 20 235 "01" "2016" "62-PERRY " 175 17 192 "01" "2016" "63-PIKE " 99 5 104 "01" "2016" "64-PORTER " 1279 121 1400 "01" "2016" "65-POSEY " 202 20 222 "01" "2016" "66-PULASKI " 163 19 182 "01" "2016" "67-PUTNAM " 391 43 434 "01" "2016" "68-RANDOLPH " 335 29 364 "01" "2016" "69-RIPLEY " 307 28 335 "01" "2016" "70-RUSH " 240 22 262 "01" "2016" "71-ST. JOSEPH " 3641 283 3924 "01" "2016" "72-SCOTT " 276 19 295 "01" "2016" "73-SHELBY " 617 53 670 "01" "2016" "74-SPENCER " 181 14 195 "01" "2016" "75-STARKE " 354 27 381 "01" "2016" "76-STEUBEN " 451 36 487 "01" "2016" "77-SULLIVAN " 258 17 275 "01" "2016" "78-SWITZERLAND " 100 6 106 "01" "2016" "79-TIPPECANOE " 1855 150 2005 "01" "2016" "80-TIPTON " 215 21 236 "01" "2016" "81-UNION " 114 8 122 "01" "2016" "82-VANDERBURGH " 2059 155 2214 "01" "2016" "83-VERMILLION " 235 19 254 "01" "2016" "84-VIGO " 1352 107 1459 "01" "2016" "85-WABASH " 450 59 509 "01" "2016" "86-WARREN " 120 20 140 "01" "2016" "87-WARRICK " 639 70 709 "01" "2016" "88-WASHINGTON " 392 35 427 "01" "2016" "89-WAYNE " 822 67 889 "01" "2016" "90-WELLS " 384 40 424 "01" "2016" "91-WHITE " 328 25 353 "01" "2016" "92-WHITLEY " 370 41 411 "01" "2016" "Total:" 88111 7307 95418 "01" "2016" end
Code:
forval yr = 16/19 { forval mnth = 01/09 { import excel "ENR_20`yr'_0`mnth’.xlsx", sheet("DISTRICT") cellrange(A10:K103) firstrow case(lower) clear rename a county_name drop anthem mdwise mhs total f g drop mchip schip gen medicaid_enroll_ch= county+ expansion gen month="0`mnth'" gen year="20`yr'" append using `in’ save `in’, replace } }
too few quotes
r(132);
0 Response to [Help] Appending Using Looping
Post a Comment